Test for hardware problems with PHYSICAL_ONLY

The DBCC CHECKDB command acquired a new option in SQL Server 2000: PHYSICAL_ONLY. This option allows DBCC CHECKDB to perform a limited series of tests to determine whether or not the database has suffered damage that occurs when there's a physical problem with the database. Such problems may include disk errors, controller issues or other hardware-based problems that usually result in data loss, but may not show up immediately.

The syntax is simple enough:

USE
DBCC CHECKDB WITH PHYSICAL_ONLY

A successful run without errors will look like this:

DBCC results for ''.
CHECKDB found 0 allocation errors and 0 consistency errors in database ''.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PHYSICAL_ONLY is designed to run with as little overhead as possible, so it only checks the physical integrity of pages, as well as indexes and allocation structures. For that reason, PHYSICAL_ONLY is a good, fast way to look for torn pages, usually the most obvious sign of a hardware problem. Subtler damage, such as the logical consistency of the database's internal structures, won't be covered by this option -- but major damage caused by hardware issues gives you something much bigger to worry about!

PHYSICAL_ONLY cannot be run wi

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Management A first look at Microsoft SQL Server 2008 R2 Maintaining high availability of SQL Server virtual machines Creating fault-tolerant SQL Server installations Using Microsoft Hyper-V for SQL Server consolidation Scaling up vs. scaling out with SQL Server 2008 Migrating to SQL Server 2008 and leveraging new features Testing a SQL Server environment before an upgrade Does upgrading to SQL Server 2008 fit your business? Meeting business needs with SQL Server full-text search Using dynamic management views to improve SQL Server index effectiveness Microsoft SQL Server Performance Monitoring and Tuning Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Determining the source of full transaction logs in SQL Server Improving SQL Server full-text search performance New GROUP BY option provides better data control in SQL Server 2008 Microsoft SQL Server 2008 Resource Governor primer Examining data files when SQL Server tempdb is full Testing transaction log autogrowth behavior in SQL Server Meeting business needs with SQL Server full-text search Using dynamic management views to improve SQL Server index effectiveness

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

th any repair options, such as REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS. If a scan with PHYSICAL_ONLY uncovers problems, you probably can't repair them on a software level. To do so might risk further damaging available data. The best plan to prevent data integrity loss is to regularly back up to another medium; running a lossy repair against a database should only be a last resort.

If you don't have a current backup, another option to consider when faced with data loss is to run DBCC CHECKTABLE with REPAIR_ALLOW_DATA_LOSS, then make a parallel restoration of the database (restore the database from its most recent backup under a different, separate name) and selectively copy over any data that changed since the last backup to the "good" copy. This allows you to work from a known good copy and preserve whatever changes were made since the last backup.

About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!

More information from SearchSQLServer.com