Repairing and Maintaining MS-SQL Server Database Files

Understanding the MS SOL Server Database Consistency Checker (DBCC)

The Database Consistency Checker (DBCC) is a tool you can use to get detailed information about database objects that the MS-SQL Server manages. In general terms, the DBCC is a set of statements (sometimes referred to as Database Console Commands), which you can use to make sure that all is well within the DBMS. As you will see from their descriptions within this tip and Tips 542-545, DBCC statements check the physical and logical consistency of the database. Many statements not only detect, but also fix the problems they detect.

Typically, you execute DBCC statements either to fix a reported problem within the DBMS (before resorting to a database restore, which may result in some data loss), or to determine the source of general DBMS "sluggishness" or stored procedure calls seemingly to yield wrong or inconsistent results.

DBCC statements fall into the following four categories:

When you perform DBCC Maintenance tasks and Validation operations, you normally want to do so when there is as little activity as possible on the MS-SQL Server. If you attempt to run consistency checks and repairs while users are making a lot of updates and changes, you may receive false errors, because many DBCC system calls require near-exclusive use of the database. Therefore, if you must run DBCC statements on a database while it is in use, check the warning and error messages reported carefully. See if any reported errors are due to user-caused deadlocks (which you learned about in Tip 356 "Understanding Deadlocks and How the DBMS Resolves Them") or other timing issues resulting from a general high-level of DBMS activity. If in doubt, (and definitely before taking drastic repair measures), use the system stored procedure SP_DBOPTION to place the database in single-user mode before running DBCC maintenance or validation statements on it.

Suppose, for example, that you want to run some consistency checks on the SQLTips database. First, take exclusive control of the database by asking all users to logout and then place the database to single-user mode with the following statement:

sp_dboption SQLTips, 'single user', TRUE

  Note 

Setting a database to single-user mode only means that one user can login to the database at a time. Therefore, if you called SP_DBOPTION while using a database other than the one you changed to single-user mode, make sure that you are the one user allowed access to the database by executing a "USE " statement immediately after you set the database to single-user mode. Thus, in the following example, you would make the preceding SP_DBOPTION call and then execute the following USE statement immediately thereafter:

use SQLTips

After you run the DBCC checks, you will want to perform any necessary maintenance or repair operations. Be sure to return the database state to multi-user mode so users can once again login to the database. To change the database back to multi-user mode, execute the following statement:

sp_dboption SQLTips, 'single user', FALSE

You will of course, substitute the name of your database for "SQLTips" in both the SP_DBOPTION call to set the database to single-user mode and the subsequent call to the same-system stored procedure to restore multi-user access.

Tips 542-545 will provide the syntax and discuss each of the DBCC statements available within the four DBCC statement categories.

Understanding DBCC Maintenance Statements

You can use the following five DBCC maintenance options to rebuild and defragment indexes, shrink database files, and update space usage tables:

The syntax of the DBCC DBREINDEX statement is

DBCC DBREINDEX (['database.owner.' [, (,]]]) [WITH NO_INFOMSGS]

where:

is the fully qualified name of the table with the indexes you want to rebuild. You can omit the database and owner and give only the table name.

You can use DBCC DBREINDEX to rebuild either an individual index for a table or all the table's indexes at once. Being able to rebuild all indexes at once is especially convenient when you perform a bulk copy of data into a table. It lets you create the PRIMARY KEY and UNIQUE indexes automatically without knowing the table's structure or constraints.

Moreover, DBCC DBREINDEX lets you recreate all indexes without having to code multiple DROP INDEX and CREATE INDEX statements. For example, to recreate all indexes on the PRODUCTS table within the NORTHWIND (sample) database with a FILLFACTOR of 50 percent, you would execute the following statement:

DBCC DBREINDEX('northwind.dbo.products','' ,50)

The syntax of the DBCC INDEXDEFRAG statement is

DBCC INDEXDEFRAG ( {||0} ,{

|

|''|} ,{ | } ) [WITH NO_INFOMSGS]

where:

|

|| is the name or numeric ID of the table or view with the index you want to defragment.

You can use DBCC INDEXDEFRAG to defragment both clustered and non-clustered indexes. DBCC INDEXDEFRAG performs two actions:

Because defragmenting a large, fragmented index may take a long time, DBCC INDEXDEFRAG reports its progress every five minutes by giving an estimated percentage completed. You can terminate the defragmentation process at any time, and the DBMS will retain any work already performed by the procedure. When deciding whether to defragment an index or rebuild it entirely, keep the following in mind:

The syntax of the DBCC SHRINKDATABASE statement is

DBCC SHRINKDATABASE ( [,] [, {NOTRUNCATE|TRUNCATEONLY}])

where:

If you want the DBMS to reduce the size of database files while leaving a certain percentage of each file "empty," execute the DBCC SHRINKDATABASE statement specifying the percentage of free space in each file. Suppose, for example, that you have a database named SQLTips with two data files and a transaction log file. Each of the three files is 20 megabytes in size and contains 12 megabytes of data. Executing the following statement then tells the DBMS to reduce each of the files to 15 megabytes:

DBCC SHRINKDATABASE (SQLTips, 20)

Each file will then have 12 megabytes of data plus three megabytes of free space (20 percent of 15 megabyte is three megabytes). The DBMS will move any data within the last five megabytes of each file to free space within the first 15 megabytes and reduce the total size of the 20-megabyte file to 15 megabytes.

If you specify the NOTRUNCATE option when executing the DBCC SHRINKDATABASE statement, the DBMS still moves data from allocated (8Kb) pages at the end of each file to unallocated (8Kb) pages at the front of the file. However, the file's size does not change. In this example, executing the following statement has the DBMS move all data to the first 12 megabytes within each file, leaving eight megabytes of free space at the end of each 20-megabyte file:

DBCC SHRINKDATABASE (SQLTips, NOTRUNCATE)

If you specify the TRUNCATE option when executing the DBCC SHRINKDATABASE statement, the DBMS does not move data within the data files. Instead, the DBMS simply truncates all unallocated (8Kb) pages from the end of each file. For example, if a 20-megabyte file has 12 megabytes of data spread across the first 17 megabytes within a file, the following statement will reduce the file's size to 17 megabytes, returning the last three megabytes of contiguous "free space" (or slack) to the operating system:

DBCC SHRINKDATABASE (SQLTips, TRUNCATEONLY)

Bear in mind that DBCC SHRINKDATABASE (unlike DBCC SHRINKFILE) will not reduce the physical size of any database files (neither data nor transaction log files) below the minimum size specified when the files were created (with a CREATE DATABASE statement) or the minimum file size later explicitly set within an ALTER DATABASE statement.

The syntax of the DBCC SHRINKFILE statement is

DBCC SHRINKFILE ( {| {[,]|[,{EMPTYFILE|NOTKUNCATE|TRUNCATEONLY}]} )

where:

You can use the DBCC SHRINKFILE statement to reduce the size of a file to smaller than the minimum size specified within the CREATE DATABASE statement used to create the file (or the explicit file size specified within an ALTER DATABASE statement). If you reduce the file's size below the minimum file size, the you specify within the DBCC SHRINKFILE statement becomes the new minimum file size for the file.

The syntax of the DBCC UPDATEUSAGE statement is

DBCC UPDATEUSAGE ( {''|0} [,{'

'|''} [,{''|''}]] ) [WITH {[COUNT_ROWS[,NO_INFOMSGS]]|NO_INFOMSGS}]

where:

| is the name of the table or indexed view for which to report the correct usage statistics.

DBCC UPDATEUSAGE will correct the ROWS, USED, RESERVED, and DPAGES columns within the SYSINDEXES table for tables, views, and clustered indexes. Size information is not stored for non-clustered indexes.

Typically, you will run DBCC UPDATEUSAGE only when you suspect the system-stored procedure SP_SPACEUSED is reporting inaccurate results. In fact, SP_SPACEUSED accepts an optional parameter to run DBCC UPDATEUSAGE before returning the space usage information for the table, view, or index. If it finds no inaccuracies within the SYSINDEXES table, DBCC UPDATEUSAGE returns no data. Conversely, if DBCC UPDATEUSAGE finds inaccuracies (which it corrects), the statement returns messages that give the rows and columns it updated within SYSINDEXES—so long as you did not specify the NO_INFOMSGS option.

543 Understanding DBCC Miscellaneous Statements

You can use the following four DBCC miscellaneous options to manage the MS-SQL Server's memory usage or display the syntax of any DBCC statement:

The syntax of the DBCC (FREE) statement is

DBCC (FREE)

where:

When you tell the DBMS to execute an extended stored procedure the DBMS loads a DLL program file into memory. For example, to execute XP_SENDMAIL the DBMS loads "sqlmap70.dll." Any DLLs that extended stored procedures load into memory remain in memory until you shut down the MS-SQL Server. To unload an extended stored procedure's DLL (to free up memory), you can execute the DBCC (FREE) statement. For example, to unload sqlmap70.dll from memory, submit the following statement to the DBMS:

DBCC xp_sendmail (FREE)

You can display a list of all the extended stored procedure DLL files currently available on the MS-SQL Server by executing SP_HELPEXTENDEDPROC.

The syntax of the DBCC HELP statement is

DBCC HELP ( ''|@|'?' )

where:

For example, to display the syntax of the DBCC statement "DBCC UPDATEUSAGE," you would submit the following DBCC HELP statement to the DBMS:

DBCC HELP ('UPDATEUSAGE')

Or, you could place the statement name into a variable (such as @DBCC_STATEMENT, for example) and use the variable in place of the string literal ('UPDATEUSAGE') by executing the following statement batch:

DECLARE @DBCC_statement SYSNAME SET @DBCC_statement = 'UPDATEUSAGE' DBCC HELP (@DBCC statement)

The syntax of the DBCC PINTABLE statement is

DBCC PINTABLE ( ,

] )

where:

is the ID number of the table to be "pinned." To determine the table ID number, call the OBJECT_ID function as SELECT OBJECT_ID('[..]

').

DBCC PINTABLE does not cause the DBMS to read a table's data into memory. Whether "pinned" or not, a table's data is read into the server's memory cache as normal while executing Transact-SQL statements. However, when the MS-SQL Server needs space to read in a new page, the DBMS will not flush the cached pages from a "pinned" table from memory. Use DBCC PINTABLE to read small, frequently-used tables into memory. That way, the DBMS reads table data into memory the first time the DBMS retrieves and/or updates it, and subsequent references the DBMS makes to the same table data will be satisfied from memory versus requiring a disk read.

  Note 

Be very careful when using the DBCC PINTABLE statement. If you pin too large a table or too many tables, you can severely degrade system performance. By pinning memory to a specific table (or set of tables) you can make large portions of the buffer cache unavailable for use to service data requests on other tables. In fact, if you have pinned a table that is larger than the buffer cache, it can fill the entire cache and thereby slow the DBMS to a crawl. If the buffer cache becomes filled, a member of the SYSADMIN role must shut down the MS-SQL Server, restart the DBMS, and then use DBCC UNPINTABLE to unpin the table (or tables).

The syntax of the DBCC UNPINTABLE statement is

DBCC UNPINTABLE ( ,] )

where:

 

is the ID number of the table to be unpinned. To determine the table ID number, call the OBJECT_ID function as SELECT OBJECT_ID('[..]

').

When you execute the DBCC UNPINTABLE statement, the DBMS does not immediately flush the table data from its cache buffers. The DBCC UNPINTABLE statement simply marks the table as unpinned, which tells the DBMS that it can remove the table's data from memory if the DBMS needs space to read in a new page from disk.

544 Understanding DBCC Status Statements

You can use the first two of the following six DBCC status statements to display the most recent statement each active user connection sent to the DBMS and the results set that the DBMS returned. The remaining four statements let you check the fragmentation statistics for indexes and table data, monitor transaction log usage, and display the connection options settings for the current session:

The syntax of the DBCC INPUTBUFFER statement is

DBCC INPUTBUFFER ( )

where:

To display the full text of the last statement a specific connection submitted to the DBMS, first execute the following stored procedure call to determine the connection's SPID:

SP_WHO

Look at the LOGINAME and HOSTNAME columns to find the row with the username on the MS-SQL Server whose last transaction you want to display. Then, use the number displayed in the row's SPID column as the for the DBCC INPUTBUFFER statement. The DBCC process will display the last statement the DBMS received from the connection. For example, to display the last statement received from the user at connection 13 you would submit the following statement:

DBCC INPUTBUFFER(13)

The syntax of the DBCC OUTPUTBUFFER statement is

DBCC OUTPUTBUFFER ( )

where:

To display the most recent results set sent from the DBMS to the user at connection 20, for example, submit the following statement to the DBMS:

DBCC OUTPUTBUFFER (20)

The DBMS will display a results set with each character the DBMS last sent to the client at connection 20. Each row within the table shows the hexadecimal values for the characters sent at the left and the ASCII representation for those characters (if any) along the right side of each row.

The syntax of the DBCC SHOWCONTIG statement is:

DBCC SHOWCONTIG [( {

|

||} [,|] )] [WITH {ALL_INDEXES|FAST[,ALLINDEXES]| TABLERESULTS[,{ALL_INDEXES}][,{FAST|ALL_LEVELS}] }]

where:

|

|| is the name of the table or indexed view for which to report fragmentation data. (You can specify the table or indexed view by name or by ID number.) If you specify neither the table nor the view name or ID, the DBMS will check and report on the fragmentation for all tables and indexed views within the database.

Index and table files become fragmented because users execute INSERT, UPDATE, and DELETE statements that over time cause unused (8Kb) pages to appear among partially-filled pages that make up the index and table data files. As data gets spread across multiple pages, it is no longer stored compactly within the minimum possible number of (8Kb) pages. DBMS performance degrades, because fragmented data and/or index files cause DBMS to perform more and more page retrievals to retrieve the same amount of data.

You can defragment table data by executing the DBCC SHRINKDATABASE statement with the NOTRUNCATE option (as you learned in Tip 542 "Understanding DBCC Maintenance Statements"). To defragment indexes, either rebuild them using the DBCC REINDEX statement or defragment them (without rebuilding them) by executing the DBCC INDEX DEFRAG statement. (You learned about both these statements in Tip 542.)

To retrieve the information you need in order to determine the fragmentation levels of all indexes and tables within a database, execute the DBCC SHOWCONTIG statement without parameters as:

DBCC SHOWCONTIG WITH ALL_INDEXES

If you want to check only the fragmentation of a single table within the database, supply the table name. For example, to check the fragmentation level of the ORDER DETAILS table within the NORTHWIND database, you would execute the following statement batch to produce the report shown in Figure 544.1:

Figure 544.1: Output from an MS-SQL Server DBCC SHOWCONTIG statement

USE NORTHWIND DBCC SHOWCONTIG('order details') WITH ALL_INDEXES

Note that you can enclose the name of the table (or view) within single quotes. In fact, you must enclose the name within quotes if the name consists of multiple words separated by spaces (such as the table named ORDER DETAILS in this example).

The fragmentation data returned by the DBCC SHOWCONTIG statement consists of the following:

When deciding whether to defragment a table or index:

If you determine a table or its indexes are fragmented, use what you learned in Tip 542 "Understanding DBCC Maintenance Statements" to either rebuild or defragment the indexes and move unused pages to the end of the table data file.

The syntax of the DBCC SQLPERF statement is:

DBCC SQLPERF (LOGSPACE)

When executed, DBCC SQLPERF (LOGSPACE) returns a table with rows that show the name of each database managed by the DBMS, the size of the transaction log file (in Megabytes), and the percentage of the transaction log file currently occupied by transaction data.

The syntax of the DBCC OPENTRAN statement is

DBCC OPENTRAN ( {''|} ) [WITH TABLERESULTS [,NO_INFOMSGS]]

where:

If you specify neither the nor the the DBMS will assume you want to display the uncommitted statements in the transaction log for the current database. You can use DBCC OPENTRAN to clear uncommitted transactions without having all users logoff or shutting down and then restarting the MS-SQL Server. Instead, you can use DBCC OPENTRAN to determine which user connections have uncommitted transactions. Then you can either have each user with an open transaction either submit a COMMIT statement or a ROLLBACK statement to close the transaction. Or, you can use the output from an SP_WHO stored procedure call to determine the user's SPID and terminate the connection if necessary.

The syntax of the DBCC USEROPTIONS is:

DBCC USEROPTIONS

When executed, DBCC USEROPTIONS returns a table such as that shown in Figure 544.2, which has a row for each session option set and its current value.

Figure 544.2: Results set from an MS-SQL Server DBCC USEROPTIONS statement

545 Understanding DBCC Validation Statements

You can use the following seven DBCC validation statements to check for and correct problems in your database files. Using DBCC validation statements, you can check the integrity of disk allocation structures, the integrity of database tables, indexes, and views, and the consistency of the data within the tables that make up the system catalog. You an also check for constraint violations, and set the value of a table's IDENTITY property. In addition to reporting problems found, most DBCC statements have "repair" options you can use to correct errors when executed in single-user mode:

The syntax of the DBCC CHECKALLOC statement is

DBCC CHECKALLOC ( '' [,{REPAIR_ALLOW_DATA_LOSS|REPAIR_FAST|REPAIR_REBUILD}] ) [WITH {[ALL_ERRORMSGS|NOINFOMSGS][,[ESTIMATEONLY]]} ]

where:

  Note 

To execute DBCC CHECKALLOC with one of the repair options (REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS), you must first set the database to be repaired to single-user mode.

The syntax of the DBCC CHECKCATALOG statement is

DBCC CHECKCATALOG ( '' ) [WITH NO_INFOMSGS]

where:

When executed, DBCC CHECKCATALOG does such things as make sure that every data type within the SYSCOLUMNS table has a matching entry within the SYSTYPES table and that every view listed in the SYSOBJECTS table has at least one column within the SYSCOLUMNS table.

The syntax of the DBCC CHECKCONSTRAINTS statement is

DBCC CHECKCONSTRAINTS [( ''|'' )] [WITH {ALL_ERRORMSGS|ALL_CONSTRAINTS}]

where:

. Or, if neither

nor are specified, tells the DBCC process to check all constraints on all tables. (If you specify a , ALL_CONSTRAINTS has no effect.)

If you specify neither a

nor a the DBCC CHECKCONSTRAINTS statement will check all enabled constraints on all tables within the current database.

For each FOREIGN KEY and CHECK constraint violation it finds, DBCC CHECKCONSTRAINTS returns the name of the table, the name of the constraint, and the data value that caused the constraint violation.

The syntax of the DBCC CHECKDB statement is

DBCC CHECKDB ( '' [,NOINDEX|{REPAIR_ALLOW_DATA_LOSS|REPAIR_FAST| REPAIR_REBUILD}] ) [WITH {[ALL_ERRORMSGS][,[NO_INFOMSGS]][,[TABLOCK]] [,[ESTIMATEONLY]][,[PHYSICAL_ONLY]] }]

where:

  Note 

To execute DBCC CHECKDB with one of the repair options (REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS), you must first set the database to be repaired to single-user mode.

When executed without the PHYSICAL ONLY option, DBCC CHECKDB is the most comprehensive DBCC validation statement in that it will identify and repair the widest possible range of errors. In short, DBCC CHECKDB validates the integrity of everything within the database. As such, if you run DBCC CHECKDB, you need not run DBCC CHECKALLOC or DBCC CHECKTABLE, because DBCC CHECKDB performs all the validation and repair operations those statements provide.

The syntax of the DBCC CHECKFILEGROUP statement is

DBCC CHECKFILEGROUP ( [{''|}] [,NOINDEX] ) [WITH {[ALL_ERRORMSGS][,[NO_INFOMSGS]][,[TABLOCK]] [,[ESTIMATEONLY]] }]

where:

DBCC CHECKFILEGROUP and DBCC CHECKDB are similar in that they both check the allocation and structural integrity of tables within the database. However, while DBCC CHECKDB checks the integrity for all tables, DBCC CHECKFILEGROUP checks only the integrity for tables either located within or that have indexes located within the file group specified by the (or ) parameter.

The syntax of the DBCC CHECKIDENT statement is

DBCC CHECKIDENT ( '' [,{NORESEED|RESEED[,]}] )

where:

is the name of the table with an IDENTITY column whose IDENTITY property you want to check or change. (You learned how to use the identity property to have the DBMS insert an incrementing non-NULL value into a table column each time you insert a row into the table in Tip 32 "Understanding the MS-SQL Server IDENTITY Property.")

There is no intrinsic requirement that the values within an IDENTITY column must be unique. However, you typically use the IDENTITY property to have the DBMS insert a non-null, incrementing value into a PRIMARY KEY or into a column with a UNIQUE constraint. In so doing, you tell the DBMS to insert a unique value by which you can identify each row you insert within a table. Therefore, be careful when using the DBCC CHECKIDENT statement to change the IDENTITY property. If the table's IDENTITY column is a PRIMARY KEY or is subject to the UNIQUE constraint, you must set the IDENTITY property equal to or higher than the maximum value already within the column. If you do not, the DBMS will at some point, generate a duplicate IDENTITY (seed) value, which will prevent users from inserting additional rows into the table.

To report the IDENTITY property's value and the maximum value within the table's IDENTITY column-without changing the IDENTITY property's value, execute the DBCC CHECKIDENT statement as:

DBCC CHECKIDENT ('

',NORESEED)

To report the IDENTITY property's value, execute the DBCC CHECKIDENT statement as:

DBCC CHECKIDENT ('

',RESEED)

If the IDENTITY property's value is less than the maximum value within the table's IDENTITY column, executing DBCC CHECKIDENT using the format shown in this example tells the DBMS to set the property's value to the maximum value within the column.

To report the IDENTITY property's current value and set it to a new value (that is, to the ) without regard to the maximum value within the table's IDENTITY column, execute the DBCC CHECKIDENT statement as:

DBCC CHECKIDENT ('

',RESEED,)

If the table's IDENTITY property has a value greater than the maximum value within the table's IDENTITY column and you want to set the IDENTITY property to the maximum value within the IDENTITY column, execute the following statement batch:

DBCC CHECKIDENT ('

',RESEED,0) DBCC CHECKIDENT ('

',RESEED)

(You would of course, substitute the name of the table with the IDENTITY property you want to adjust for

and the actual value you want the IDENTITY property to have for in the previous statements.)

The syntax of the DBCC CHECKTABLE statement is

DBCC CHECTABLE ( '

'|'' [,NOINDEX|| (REPAIR_ALLOW_DATA_LOSS|REPAIR_FAST|REPAIR_REBUILD}] ) [WITH {[ALL_ERRORMSGS][, [NO_INFOMSGS]][,[TABLOCK]] [,[ESTIMATEONLY]][,[PHYSICAL_ONLY]] }]

where:

| is the name of the table or indexed view that you want the DBCC process to check for structural integrity errors. (While DBCC CHECKTABLE checks the linkages and sizes of TEXT, NTEXT, and IMAGE pages, it does not check the consistency of all allocation structures in the database; you must run DBCC CHECKALLOC or DBCC CHECKDB to do that verification.)

  Note 

To execute DBCC CHECKTABLE with one of the repair options (REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS), you must first set the database to be repaired to single-user mode.

If you want you to check the integrity of all tables within a database, use DBCC CHECKDB rather than DBCC CHECKTABLE.

Chapter 23 Writing Advanced Queries and Subqueries

Related

Категории