Maintenance DBCC Commands

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Monitoring & Tuning - DBCC Commands

Contents

Maintenance DBCC Commands

Maintenance DBCC statements are used to gather information about the general health of the database as well as specific database objects and to repair minor database corruption issues.

The following maintenance DBCC statements are covered in DBCC Commands Used for Maintenance; please refer to that section for detailed explanation and examples of those commands.

  • DBCC CHECKALLOC
  • DBCC CHECKCATALOG
  • DBCC CHECKCONSTRAINTS
  • DBCC CHECKDB
  • DBCC CHECKFILEGROUP
  • DBCC CHECKTABLE
  • DBCC CLEANTABLE
  • DBCC DBREINDEX
  • DBCC INDEXDEFRAG
  • DBCC NEWALLOC
  • DBCC SHOWCONTIG
  • DBCC SHOW_STATISTICS
  • DBCC SHRINK_DATABASE
  • DBCC SHRINKFILE

This topic covers the following undocumented commands: LOGINFO, ERRORLOG, DBREINDEXALL and DETACHDB.

DBCC LOGINFO

This command accepts a single optional parameter of either database name or database ID. DBCC LOGINFO returns information about virtual log files within the physical transaction log. If database name is not specified then information is returned about the current database. DBCC LOGINFO returns the following information:

Column Explanation
FileID Physical log file identifier from sysfiles
FileSize Virtual log file size
StartOffset Beginning point of the virtual log file
FSeqNo Virtual log file's sequence number
Status Whether the virtual file contains the active part of the log (logical log). 0 means that virtual file does not contain the active portion of the log; 2 means that it does
Parity Parity information for virtual log file
CreateLSN Log sequence number that began the virtual log file


The following is the example of results from DBCC LOGINFO: 
FileID  FileSize  StartOffset  FSeqNo  Status  Parity  CreateLSN  
2       253952    8192         918829  0       128     0  
2       253952    262144       918828  0       128     0  
2       253952    516096       918827  0       64      0  
2       278528    770048       918826  0       64      0  
6       2555904   8192         918825  0       64      0  
6       2555904   2564096      918824  0       64      916687000000037000000  
6       2555904   5120000      918835  2       128     916688000000072000000  
6       2809856   7675904      918834  2       128     916690000000008000000  
7       2555904   8192         918833  2       64      916691000000037000000  
7       2555904   2564096      918832  0       64      916692000000060000000

The status column of DBCC LOGINFO's output is perhaps the most useful bit of information. If this column contains zero, then the transaction log files can be shrunk. If this column contains 2, then there is no room for shrinking the transaction log since the last virtual log file contains active transactions.

DBCC ERRORLOG

This statement takes no parameters and is used to reinitialize the SQL Server error log. If you restart SQL Server frequently, you might have noticed that SQL Server reinitializes the error log each time it is started. However, if your log grows beyond 1 million bytes, it takes a considerable amount of time to open the log. As a database administrator you want to have access to the log at all times. Therefore if the error log tends to grow fast, be sure to execute this command regularly.

DBCC DBREINDEXALL

This command is identical to <a href="DBM3#B11">DBCC DBREINDEX]] except that it rebuilds all indexes within the specified database. You can supply a database name or ID as the parameter, as in DBCC DBREINDEXALL('pubs').

DBCC DETACHDB

Although undocumented, this command is used by the sp_detach_db system procedure to detach a particular database from the current instance of SQL Server. The database name must be in single quotes.

Retrieved from "http://sqlserverpedia.com/wiki/Maintenance_DBCC_Commands"