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:

• Maintenance— Perform maintenance tasks on a database, indexes within the database, or on the physical files that make up the logical file group in which the database is stored.
• Miscellaneous— Perform miscellaneous tasks such as displaying syntax information for a specified DBCC statement, changing the way the DBMS handles the storage of table data in memory, and unloading a DLL (extended-system stored procedure) from memory.
• Status— Check the status of such things as the amount of empty space within the transaction log, open transactions, stored procedures within the stored procedure cache and so on.
• Validation— Perform validation (and repair) operations on the database or its system tables, a table, index, or catalog within the database, on the files that make up the file group, or the allocation of database pages.

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:

• DBREINDEX—Rebuild one or more indexes for a table within a database.
• INDEXDEFRAG—Defragment clustered and secondary indexes on a specific table or view.
• SHRINKDATABASE—Reduces the file size of the physical data files that make up the database.
• SHRINKFILE—Used to reduce the file size of individual physical database files or of the transaction log file.
• UPDATEUSAGE—Reports and corrects inaccuracies in the SYSINDEXES table.

The syntax of the DBCC DBREINDEX statement is

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

where:

•  
• database.owner.

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.

• is the name of the index that you want the DBMS to rebuild. If you omit the or specify it as '', the DBMS will rebuild all the table's indexes.
• is the percentage of space on each index page that the DBMS is to use for storing data. Each time an index page gets full (with data), the DBMS must split the page in two. This impacts performance as page-splits are expensive in terms of system resources. Thus, the lower fill factor (percentage), the more disk space the index consumes, but the less often the DBMS must add new pages to it. For more information on the "fill factor," see the FILLFACTOR section within Tip 162 "Understanding MS-SQL Server CREATE INDEX Statement Options." The you supply here becomes the new default FILLFACTOR for the index. If you supply a of zero (0), the DBMS uses the original FILLFACTOR defined for the index (again, as described in Tip 162).
• tells the DBCC to suppress all informational messages—those with severity levels from 0 through 10.

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:

• ||0 is the name of the database with the index you want the DBMS to defragment. As shown here, you can specify the database by name or by ID number. Specifying an ID of zero (0) tells the DBMS the table with the index is within the current database.

|

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

• | is the name or ID number of the index the DBMS is to defragment.
• _INFOMSGS> tells the DBMS to suppress all informational messages—those with severity levels from 0 through 10.

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

• It defragments the leaf level of an index so that the physical order of pages matches the left-to-right order of the leaf nodes, which improves the performance of an index scan.
• It compacts the index, moving data from one index and taking the FILLFACTOR into account. The DBMS removes any "empty" pages created as a result of shifting data from one index page to another.

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:

• DBCC INDEXDEFRAG (unlike DBCC DBREINDEX) does not hold locks long term and thus will not block users from executing queries and updates while it defragments an index.
• You can defragment a relatively unfragmented index in much less time than it will take to rebuild the same index. Conversely, a very fragmented index typically takes much longer to defragment than to rebuild.
• Defragmenting an index is always a fully-logged process. It inserts more entries into the database transaction log than rebuilding the the same index.

The syntax of the DBCC SHRINKDATABASE statement is

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

where:

• is the name of the database whose files you want the DBMS to shrink (in size). The DBMS shrinks database files by removing the empty space available for additional data within each database file.
• is the percentage of empty space the DBMS is to leave within the file after performing the "shrink" operation.
• NOTRUNCATE is the NOTRUNCATE option causes the DBMS to leave "freed" space within the database files. Omit NOTRUNCATE and specify only the if you want DBCC SHRINKDATABASE to reduce the size of database files.
• TRUNCATEONLY releases the "freed" space back to the operating system for use. If you want to leave a percentage of free space within database files (as specified by ), omit the TRUNCATEONLY option. If you specify both (a and TRUNCATEONLY), the DBMS ignores the and truncates database files, returning any free space at the end of each file to the operating system.

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:

• | is the name of the file whose size you want the DBMS to reduce. You can specify the file by name or by ID number.
• size> is the integer value that specifies the "target" size for the file in megabytes. For example, if a 20 megabyte file has 12 megabytes of data and you specify a of 14, the DBMS will move the data within the last six megabytes of the file to unused (8Kb) pages within the first 14 megabytes, and truncate the file at 14 megabytes. The DBMS will not however, make a file smaller than the size required to hold all data within the file. For example, given the same 20 megabyte file with 12 megabytes of data, specifying a of 10 will reduce the file's size to 12 megabytes (not 10 megabytes).
• EMPTYFILE tells the DBMS to move all data within the target file to other files within the same file group, leaving an empty file which you can drop using the ALTER DATABASE statement. After you specify the EMPTYFILE option for a file, MS-SQL Server will no longer place any data into the file.
• NOTRUNCATE tells the DBMS to move all allocated (8Kb) pages past the to unallocated (8Kb) pages prior to within the file. If you omit , the DBMS moves all allocated (8Kb) pages to the front of the file such that all free space (or slack) remains at the end of the file. When specified, NOTRUNCATE prevents the DBMS from releasing any "free" space within the file to the operating system. As such, the file size will not change.
• TRUNCATEONLY tells the DBMS to shrink the file size to the last allocated extent. Thus, if the 20-megabyte file has 12 megabytes of data spread across the first 15 megabytes, specifying TRUNCATEONLY will reduce the file's size to 15 megabytes. The DBMS does not shift any data within the file.

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:

• |0 is the name of the database for which to report the correct file usage statistics. Zero (0) means use the current database.

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

• | is the name or ID number of the index for which to report usage statistics. If you do not specify an (or an ), the DBMS will update the statistics for all indexes on the table or view.
• COUNT_ROWS tells the DBMS to update the ROWS column of rows within the SYSINDEXES table for the current table or view specified with the current count of the rows within the table or view. If you do not specify a table or view, the DBMS will update the ROWS column within the corresponding SYSINDEX rows for each table and view within the database. The COUNT_ROWS option only affects the ROWS column of SYSINDEX rows in which the INDID column is 0 or 1.
• NO_INFOMSGS tells the DBMS to suppress the display of all informational messages.

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:

• (FREE)-Unloads the DLL for an extended stored procedure from memory.
• HELP-Returns the syntax for the specified DBCC statement.
• PINTABLE-Tells the DBMS not to flush data for the specified table from memory.
• UNPINTABLE-Tells the DBMS it can flush table pages from the buffer cache as necessary.

The syntax of the DBCC (FREE) statement is

DBCC  (FREE)

where:

• - Is the name of the extended stored procedure dynamic-link library (DLL) file you want the DBMS to remove from memory.

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:

• |@| is a (quoted) literal string ('') or a variable (@) with the name of the DBCC statement, that is, the keyword following "DBCC" in a DBCC statement.
• ? tells the DBMS to return a list of all DBCC statements for which you can obtain "help" information.

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 database with the table to be "pinned." To determine the database ID number, call the DB_ID function as SELECT DB_ID( ['']).

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 database with the table to be unpinned. To determine the database ID number, call the DB_ID function as SELECT DB_ID( ['']).
•  

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:

•  
• INPUTBUFFER-Displays the last statement the MS-SQL Server received from a particular client connected to the DBMS.
•  
• OUTPUTBUFFER-Displays in hexadecimal and ASCII, the last set of results sent to a particular client attached to the DBMS.
•  
• SHOWCONTIG-Displays fragmentation information for a table and its indexes.
•  
• OPENTRAN-Displays information about the oldest active transaction, if any, being processed by the DBMS.
•  
• SQLPERF-Displays statistics about the transaction log in all database managed by the MS-SQL Server.
•  
• USEROPTIONS-Displays the user options set for the current connection with the DBMS.
•  

The syntax of the DBCC INPUTBUFFER statement is

DBCC INPUTBUFFER (  )

where:

• is the ID number (SPID) of the user connection to the DBMS. You can call the system stored procedure SP_WHO to get the list of processes currently running on the DBMS. Each row of the results set returned by SP_WHO gives the system process ID (SPID), current status, user's login name, name of the database the process is using, and the name of the last request submitted to the DBMS for execution.

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:

• is the ID number (SPID) of the user connection to the DBMS. You can call the system stored procedure SP_WHO to get the list of processes currently running on the DBMS. Each row within the results set returned by SP_WHO gives the system process ID (SPID), current status, login name, name of the database the process is using, and the name of the last request submitted to the DBMS for execution.

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.

• | is the name or ID number of the index for which to report fragmentation statistics. If you do not specify an (or an ), the DBMS will report fragmentation stats for the base index (that is, for the PRIMARY KEY) on the specified table or view.
• FAST tells the DBCC process to perform a fast scan and report minimal information. When performing a fast scan, the DBMS does not read the "leaf" or data-level pages within the index.
• TABLERESULTS tells the DBCC process to display the fragmentation information returned in tabular form versus a summary report. In addition to changing the output format (from report to table), each row within the results table includes the table name, index name, record size, page count, and disk extent data, in addition to all the fragmentation data returned when you do not specify the TABLERESULTS option.
• ALL_INDEXES tells the DBCC to display fragmentation information about all indexes for a table, even when a specific index is specified with or .
• ALL_LEVELS tells the DBCC to produce a row of output for each level within the index tree for each index processed. You can only specify the ALL_LEVELS option if you also specify the TABLERESULTS option and do not specify the FAST option. When you do not specify ALL_LEVELS, the DBMS only processes the table data and the highest level within the index tree, the leaf level.

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:

• Pages Scanned- The number of (8Kb) pages the table or index contains. (TABLERE-SULTS column, PAGES.)
• Extents Scanned- The number of disk extents the table or index contains. The operating system adds space to a disk file one "extent" worth of bytes at a time. You set the size of an extent when you format the disk for the operating system. The optimal extent size for MS-SQL Server running under the Windows NT, NTFS file system is 64Kb. (TABLERESULTS column, EXTENTS.)
• Extent Switches- The number of times the DBCC process had to move from one disk extent to another while traversing the (8Kb) pages within the table or index. (TABLERESULTS column, EXTENTSWITCHES.)
• Avg. Pages per Extent- The average number of (8Kb) pages stored within each (64Kb) disk extent.
• Scan Density (Best Count, Actual Count)- Best Count is the number of extent changes the DBCC process would encounter if everything were contiguously linked (that is, if there were no fragmentation within the data file or index). Actual Count is the actual number of extent changes the DBCC process encountered when processing the table or index. Scan Density is the Best Count divided by the Actual Count, expressed as a percentage. If Scan Density is 100 percent, everything is contiguous (that is, there is no fragmentation); if Scan Density is less than 100 percent, some fragmentation exists. (TABLERESULTS columns, SCANDENSITY, BESTCOUNT, ACTUALCOUNT.)
• Logical Scan Fragmentation- The percentage of times when the next page indicated in an IAM (Indexed Access Method) is a different (8Kb) page than the page pointed to by the next page pointer in the index leaf page. (TABLERESULTS column, LOGICALFRAG-MENTATION.)
• Extent Scan Fragmentation- The percentage of times the disk extent with the current (8Kb) page for an index is not physically the next extent after the extent that contains the previous (8kb) page for an index. (TABLERESULTS column, EXTENTFRAGMENTATION.)
• Avg. Bytes Free per Page- The average number of free (unused) bytes on the (8Kb) pages scanned. Although less unused space per page is better, the row size can affect the amount of free space in that the larger the row size, the more unused space per (8Kb) page. (TABLERESULTS column, AVERAGEFREEBYTES.)
• Avg. Page Density (full)- The page density expressed as a percentage that takes into account the table's row size. As such, this is a more accurate indication of how full the (8Kb) pages in the table or index are. (TABLERESULTS column, AVERAGEPAGEDENSITY.)

When deciding whether to defragment a table or index:

• Check the Avg. Page Density (full), it should be a high percentage.
• Check Logical Scan Fragmentation and Extent Scan Fragmentation, both these numbers should be as close to zero (0) as possible (0-10 percent is an acceptable range). Bear in mind Extent Scan Fragmentation will be high if the index spans multiple files.
• Compare Extent Switches and Extents Scanned-ideally, both numbers will be equal. Note this indication of fragmentation does not work for an index that spans multiple files.
• Check the Scan Density-it should be as high as possible.

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:

• | is the name (or numeric ID) of the database whose transaction log you want to query for a list of transactions not yet written permanently to the database.
• tells the DBMS to format the results set in a tabular format you can easily load into a table. If you do not specify the TABLERESULTS option, the DBMS returns results set as a "readable" report versus in tabular form.
• tells the DBMS to suppress all informational messages.

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:

• CHECKALLOC-Checks (and can repair) the disk allocation structures for a specified database.
• CHECKCATALOG-Checks for consistency in and between system tables within a database.
• CHECKCONSTRAINTS-Checks the integrity of one or all FOREIGN KEY and CHECK constraints on one or all tables in a database.
• CHECKDB-Checks (and can repair) both the disk allocation structures and the integrity of all objects within the database.
• CHECKFILEGROUP-Checks (and can repair) the disk allocation and structural integrity of all tables in within a file group.
• CHECKIDENT-Checks and lets you set the value of the IDENTITY property for tables that have an IDENTITY column.
• CHECKTABLE-Checks (and can repair) the integrity of the data within a table.

The syntax of the DBCC CHECKALLOC statement is

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

where:

• is the name of the database for which you want the DBCC to check for allocation errors. When it is run with the REPAIR_ALLOW_DATA_LOSS option, DBCC CHECKALLOC will ensure that the index structures that tell the DBMS where to find data within the database file on disk actually point to table or index data and that the disk extent in which the data resides is allocated to the database object's storage area within the database file.
• REPAIR_FAST tells the DBCC process to perform minor, non-time-consuming repairs such as removing extra keys in non-clustered indexes. Repairs performed under the REPAIR_FAST option will not result in any data loss.
• REPAIR_REBUILD tells the DBCC process to perform the same repairs as specified by the REPAIR_FAST option plus time-consuming repairs such as rebuilding indexes. Repairs performed under the REPAIR_REBUILD option will not result in any data loss.
• REPAIR_ALLOW_DATA_LOSS tells the DBCC process to perform the same repairs as it would when you specify the REPAIR_REBUILD option plus repair allocation errors, structural row errors, and delete invalid text objects. Repairs performed under the REPAIR_ALLOW_DATA_LOSS option may (as its name implies) result in some data loss if the DBCC process must remove corrupted data.
• ALL_ERRORMSGS tells the DBCC process to display all error messages. If NO_INFOMSGS is specified and ALL_ERRORMSGS is not, DBCC CHECKALLOC will only display the first 200 error messages per object.
• NO_INFOMSGS tells the DBCC process to suppress all informational messages (and display only error messages).
• ESTIMATE_ONLY tells the DBCC process to display the estimated amount of space within TEMPDB that the DBCC CHECKALLOC statement will require when executed with the options and parameters specified.

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:

• is the name of the database in which you want the DBCC to check the consistency of the data within the system tables.
• NO_INFOMSGS tells the DBCC process to suppress all informational messages in the report of space used by the system catalog. If you specify the NO_INFOMSGS option, DBCC CHECKCATALOG will display only the first 200 error messages the process encounters. To display all error messages, omit the NO_INFOMSGS option from the DBCC CHECKCATALOG statement.

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:

•  
• is the name of the table with the constraints you want the DBCC process to check. If you specify a table name, the DBCC checks all the table's enabled constraints.
•  
• is the name of the constraint you want the DBCC to check.
•  
• ALL CONSTRAINTS tells DBCC to check all constraints (both enabled and disabled) on the table specified by

. 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.)

• ALL ERRORMSGS tells the DBCC process to display all rows that violate the constraints checked. If you omit the ALL_ERRORMSGS option, the DBCC process will report only the first 200 constraint violations within each table it checks.

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:

• is the name of the database in which you want the DBCC to check for allocation and structural integrity errors.
• NOINDEX specifies that non-clustered indexes on non-system (that is, on user-defined) tables should not be checked.
• REPAIR_FAST tells the DBCC process to perform minor, non-time-consuming repairs such as removing extra keys in non-clustered indexes. Repairs performed under the REPAIR_FAST option will not result in any data loss.
• REPAIR_REBUILD tells the DBCC process to perform the same repairs as specified by the REPAIR_FAST option plus time-consuming repairs such as rebuilding indexes. Repairs performed under the REPAIR_REBUILD option will not result in any data loss.
• REPAIR_ALLOW_DATA_LOSS tells the DBCC process to perform the same repairs as it would when you specify the REPAIR_REBUILD option plus repair allocation errors, structural row errors, and delete invalid text objects. Repairs performed under the REPAIR_ALLOW_DATA_LOSS option may (as its name implies) result in some data loss if the DBCC process must remove corrupted data.
• ALL_ERRORMSGS tells the DBCC process to display all error messages. If NO_INFOMSGS is specified and ALL_ERRORMSGS is not, DBCC CHECKDB will only display the first 200 error messages per object.
• NO_INFOMSGS tells the DBCC process to suppress all informational messages (and display only error messages).
• TABLOCK tells the DBCC to obtain a shared table locks (versus row or perhaps, page locks). Specifying the TABLOCK option makes DBCC CHECKDB run faster on a DBMS that has a heavy processing load. However, issuing table locks will decrease concurrency as users are forced to wait for the DBCC process to complete its work on the entire table (versus on a single row or 8Kb page of rows).
• ESTIMATE_ONLY tells the DBCC process to display the estimated amount of space within TEMPDB that the DBCC CHECKDB statement will need when executed given the options and parameters specified.
• PHYSICAL_ONLY tells the DBCC process to check only the physical consistency of the database by checking the structure of page and record headers, the database file allocation structures, and the allocated disk extents for hardware failures that can corrupt data. PHYSICAL_ONLY implies that you want NO_INFOMSGS, and you cannot specify PHYSICAL_ONLY with any of the three repair options (REPAIR_FAST, or REPAIR_REBUILD, REPAIR_ALLOW_DATA_LOSS).

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:

• is the name of the file group for which you want to check table allocation and structural integrity. (You learned how to put database objects within file groups in Tip 496 "Adding Files and Filegroups to an Existing Database" and Tip 497 "Using the MS-SQL Server Enterprise Manager to Add Files and Filegroups to an Existing Database.")
• is the unique integer ID assigned to each file group within a database. To determine the filegroup ID, first find the filegroup's name within the GROUPNAME column of the SYSFILEGROUPS table. Then, look at the integer within the GROUPID column of that row for the filegroup ID.
• NOINDEX specifies that non-clustered indexes on non-system (that is, on user-defined) tables should not be checked. (DBCC CHECKFILEGROUP always checks all indexes on system tables when run on the PRIMARY file group.)
• ALL_ERRORMSGS tells the DBCC process to display all error messages. If NO_INFOMSGS is specified and ALL_ERRORMSGS is not, DBCC CHECKFILEGROUP will only display the first 200 error messages per table.
• NO_INFOMSGS tells the DBCC process to suppress all informational messages (and display only error messages).
• TABLOCK tells the DBCC process to obtain a shared table lock (versus row or perhaps, a page lock) while checking (and repairing) a table. Specifying the TABLOCK option makes DBCC CHECKFILEGROUP run faster on a DBMS with a heavy processing load. However, issuing table locks decreases concurrency as other users are forced to wait for the DBCC process to complete its work on the entire table (versus on a single row or 8Kb page of rows) before they can work with the data in the table.
• ESTIMATE_ONLY tells the DBCC process to display the estimated amount of space in TEMPDB the DBCC CHECKFILEGROUP statement will need when executed with the options and parameters specified.

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.")

• NORESEED specifies that the DBCC process should report but not change the value of the IDENTITY property.
• RESEED specifies that the DBCC process should correct the value of the IDENTITY property. If the IDENTITY property is less than the highest value within the table's IDENTITY column, DBCC CHECKIDENT will set the IDENTITY property to maximum value within the column. Conversely, if the IDENTITY property has a value greater than or equal to the maximum value within the table's IDENTITY column, DBCC CHECKIDENT will not change the IDENTITY property's value.
• is the value to which the DBCC process will set the identity property for the table. If specified, the must follow the RESEED option.

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.)

• NOINDEX specifies that non-clustered indexes on non-system (that is, on user-defined) tables should not be checked.
• instructs the DBCC process to check only the integrity of the table and a specific index (given by ).
• REPAIR_FAST tells the DBCC process to perform minor, non-time-consuming repairs such as removing extra keys in non-clustered indexes. Repairs performed under the REPAIR_FAST option will not result in any data loss.
• REPAIR_REBUILD tells the DBCC process to perform the same repairs as specified by the REPAIR_FAST option plus time-consuming repairs such as rebuilding indexes. Repairs performed under the REPAIR_REBUILD option will not result in any data loss.
• REPAIR_ALLOW_DATA_LOSS tells the DBCC process to perform the same repairs as it would when you specify the REPAIR_REBUILD option plus repair allocation errors, structural row errors, and delete invalid text objects. Repairs performed under the REPAIR_ALLOW_DATA_LOSS option may (as its name implies) result in some data loss if the DBCC process must remove corrupted data.
• ALL_ERRORMSGS tells the DBCC process to display all error messages. If NO_INFOMSGS is specified and ALL_ERRORMSGS is not, DBCC CHECKTABLE will only display the first 200 error messages per object.
• NO_INFOMSGS tells the DBCC process to suppress all informational messages (and display only error messages).
• TABLOCK tells the DBCC process to obtain a shared table lock (versus row or perhaps, a page lock). Specifying the TABLOCK option makes DBCC CHECKTABLE run faster on a DBMS with a heavy processing load. However, issuing table locks decreases concurrency as other users are forced to wait for the DBCC process to complete its work on the entire table (versus on a single row or [8Kb] page of rows) before they can work with the data in the table.
• ESTIMATE_ONLY tells the DBCC process to display the estimated amount of space within TEMPDB that the DBCC CHECKTABLE statement must have when executed with the options and parameters specified.
• PHYSICAL_ONLY tells the DBCC process to check only the physical consistency of the table by checking the structure of page and record headers, the table's file allocation structures, and for common hardware failures that can corrupt data. PHYSICAL_ONLY implies that you want NO_INFOMSGS and you cannot specify PHYSICAL_ONLY with any of the three repair options (REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ ALLOW_DATA_LOSS).

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