Troubleshooting DBCC Commands
From SQLServerPedia
|
See Also: Main_Page - Monitoring & Tuning - DBCC Commands
Troubleshooting DBCC CommandsDBCC statements used for troubleshooting get you the information necessary to correct a particular problem, and in some cases will actually fix an issue. The boundaries between various categories of DBCC statements are rather loose, so commands used for tuning or maintenance might also be used for troubleshooting. NOTE: Undocumented commands are marked with ***. DBCC CHECKIDENTThis command checks the current identity value for a specified table and, if necessary, re-seeds the identity.
DBCC CHECKIDENT('employees', NORESEED)
Results: Checking identity information: current identity value '3', current column value '3'.The next statement changes the current identity value to 200: DBCC CHECKIDENT('employees', RESEED, 200)
Results: Checking identity information: current identity value '3', current column value '200'. One problem with DBCC CHECKIDENT is that it lets you reset the identity to a value that already exists in the table. For instance, if employee number 3 already exists, DBCC CHECKIDENT will still let you set the IDENTITY seed to 3. The trouble comes from the fact that most identity columns have unique constraints - they're often used as primary keys - and they cannot accept duplicates. Therefore, the next time you attempt inserting an employee, you're likely to encounter primary key violation errors. To avoid such pitfalls, be sure to check for the existence of a particular value before using it for reseeding identity columns. DBCC CHECKIDENT can be particularly useful during database development. Suppose that you have a table with an identity column and you want to get rid of the test data prior to putting the database into production. You want to get rid of the test data, but you want to maintain a few records without messing up the sequence of identities. You can delete un-needed records and re-seed the identity column of such table. Note that truncating a table automatically resets the identity column to its original seed. DBCC dllname (FREE)This command is used to remove a dynamic link library (DLL) file for an extended stored procedure from memory. When an extended stored procedure is executed, SQL Server loads it's DLL into memory. The DLL is not removed from memory until SQL Server is shut down; therefore if you execute numerous extended procedures, you might experience some performance degradation unless the DLL's are removed from memory. This command only accepts a single DLL name without an extension as a parameter. For example, the following will remove the DLL of xp_cmdshell called xplog70.dll from memory:DBCC xplog70 (FREE) DBCC INPUTBUFFERThis command displays the first 255 characters of the last command executed by the specified connection. The only parameter accepted by DBCC INPUTBUFFER is a server process ID (SPID). This command is particularly useful for troubleshooting blocking problems. Although you don't always get the complete set of commands executed by the offending connection, INPUTBUFFER helps deduce what the connection was doing when it caused problems. For instance, the following command returns 255 characters of the last batch executed by process 54:DBCC INPUTBUFFER(54)Results: EventType Parameters EventInfo Language Event 0 DECLARE @int INTDECLARE my_cursor CURSOR FOR SELECT qty FROM sales_history OPEN my_cursor FETCH NEXT FROM my_cursor INTO @int WHILE @@FETCH_STATUS = 0 BEGIN SELECT @int FETCH NEXT FROM my_cursor INTO @int END CLOSE my_cursor DEALLOC DBCC FLUSH***DBCC FLUSH "flushes" the data or log of a particular database to disk. That is, log or data records are actually saved to disk instead of hanging out in cache. The example below flushes data of the pubs database (DB_ID = 5) to the disk:DBCC FLUSH('data', 5)
DBCC OPENTRANThis command displays the oldest active (open) transaction within the specified database. DBCC OPENTRAN can also display the oldest distributed and non-distributed replicated transactions if the database is published for transactional replication. If there are no open transactions then this command returns an informational message.
DBCC OPENTRANResults: No active open transactions.The next statement returns open transactions for database ID 12: DBCC OPENTRAN(12)Results: Transactions information for database 'test'.Replicated Transaction Information: Oldest distributed LSN : (921902:522:1) Oldest non-distributed LSN : (921902:543:1) DBCC OUTPUTBUFFERThis command returns the current output buffer - that is the data returned to a specified server process id (SPID). The output of DBCC OUTPUTBUFFER contains hexadecimal as well as ASCII characters, so it is somewhat difficult to decipher. DBCC OUTPUTBUFFER returns 256 rows of output, including the data returned by the last query, and any previous queries up to 256 rows. For instance, the following output is for the connection that executed "SELECT * FROM authors":DBCC OUTPUTBUFFER(57) DBCC TRACEON | DBCC TRACEOFF | DBCC TRACESTATUSThese commands are used to turn certain trace flags on or off and to check the status of a particular trace flag(s). The syntax of these commands is very simple - they accept the number of the trace flag(s) you wish to check, turn on or off. For example, the following script turns on trace flags 3604 and 2520, checks their status and turns them off:DBCC TRACEON(3604, 2520) DBCC TRACESTATUS(3604, 2520) DBCC TRACEOFF(3604, 2520)Results: TraceFlag Status --------- ------ 3604 1 2520 1 DBCC EXTENTINFO***This command returns information about each extent allocated to the database or a particular object within a database. The full syntax of the command is as follows:
DBCC EXTENTINFO('pubs')
Each extent consists of 8 data pages (each is 8Kb), and some of these pages might not be completely full. This is especially true for TEXT, NTEXT and IMAGE data types since this data is typically stored separately from the table that contains the TEXT or IMAGE data. DBCC EXTENTINFO can be used to find out whether some of the extents occupied by TEXT and IMAGE data are not full and can be compacted. To find out whether TEXT or IMAGE data can be compacted you should create a work table as follows:CREATE TABLE extent_info ( file_id BIGINT,page_id BIGINT,pg_alloc TINYINT,ext_size SMALLINT,obj_id BIGINT,index_id TINYINT,pfs_bytes VARBINARY(4000))Next, you should create a stored procedure that executes DBCC EXTENTINFO, as follows: CREATE proc extentinfo AS DBCC EXTENTINFO(0)Then you can populate the extent_info table with the output of DBCC EXTENTINFO: INSERT extent_info EXEC extentinfo At this point you can run the following query: SELECT [file_id], obj_id,index_id,ext_size,'actual extent count' = COUNT(*),'actual page count' = sum(pg_alloc),'possible extent count' = CEILING(SUM(pg_alloc)*1.0/ext_size), 'possible extents over actual extents' = (CEILING(SUM(pg_alloc)*1.00/ext_size)*100.00) / COUNT(*) FROM extent_info WHERE ext_size <> 1 AND index_id = 255 -- TEXT or IMAGE column GROUP BY [file_id],obj_id, index_id, ext_size HAVING COUNT(*)-CEILING(SUM(pg_alloc)*1.0/ext_size) > 0 ORDER BY obj_id, index_id, [file_id] If "possible extents over actual extents" is less than 75, the database file can be shrunk by moving data to a new table, dropping the existing table and renaming the new table. DBCC LOG***Perhaps the most mysterious undocumented statement, DBCC LOG allows you to read the transaction log of your database. However, the mystery comes from the fact that DBCC LOG doesn't really give you Transact-SQL statements sent to the server. Unfortunately, SQL Server does not provide a built-in way of examining the individual statements in the transaction log. The only required parameter is the database ID. You can include the desired level of detail by specifying the output type - 0, 1, 2, 3, 4 or -1. The output type of 0 (which happens to be the default) gives you minimal information, each subsequent integer providing additional columns. An output type of -1 adds the hexadecimal dump of the transaction log's rows along with adding rows for checkpoints and database version to the output - this option generates the largest output from this command. The remaining options let you specify particular log records or pages that you wish to return with DBCC LOG. With maximum output from DBCC LOG you get the following columns:
DBCC LOG ( 4, 0) DBCC DBRECOVER***This command runs the recovery process on a specified database, rolling forward committed transactions and rolling back the uncommitted ones. The command takes two parameters: database name, and optionally an "IgnoreErrors" flag. As the name implies, the latter parameter can be turned on to ignore errors during recovery. For example:DBCC DBRECOVER('grocery')
Results: 9 transactions rolled forward in database 'grocery' (8). 0 transactions rolled back in database 'grocery' (8). DBCC REBUILD_LOG***NOTE: Be forewarned that REBUILD_LOG might cause a loss of transactional consistency of your database as explained below. Use this command with extreme caution and as a last resort. This command can be used to rebuild the transaction log if the log file has been deleted while SQL Server service was stopped. Typically, only production DBA's should have access to the file system of the production servers, so this problem should be limited to development or test environments. If you do experience the event of a log file being deleted, SQL Server is likely to mark your database suspect as soon as the service is started. You should first attempt to recover the deleted file - it might still reside in the recycle bin. If you do find the log file, stop the SQL Server service, recover the log file and start SQL Server again. At this point the problem should be solved. If you cannot find the deleted file, things will get more complicated. You must recreate the transaction log using the DBCC REBUILD_LOG command. However, you must put the database in "bypass recovery" status before you can rebuild the transaction log file. To do so, execute the following set of statements:Sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDEUPDATE master..sysdatabases SET status = status | 32768 WHERE name = 'pubs' At this point, your database (in this example, pubs) will be set in bypass recovery mode (also referred to as emergency mode) and its use will be restricted to DBO. Next, you can execute DBCC REBUILD_LOG to create a new log file for the database. This command accepts a required parameter of database name and an optional parameter of log file name. Note that you must be in the master database prior to executing DBCC REBUILD_LOG.USE master GODBCC REBUILD_LOG('pubs', 'c:\pubs.ldf')
Results: Warning: The log for database 'pubs' has been rebuilt. Transactional consistency has been lost. DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset, and extra log files may need to be deleted.As the warning indicates, you should examine the consistency of your database by running DBCC CHECKDB immediately after rebuilding the log file: DBCC CHECKDB('pubs')
Since you are creating a new log file, you might actually damage the database because some uncommitted transactions may have changed some of the data pages leaving the database in an inconsistent state. All uncommitted transactions will be lost forever. If you do experience any errors with DBCC CHECKDB, be sure to immediately BCP the data out to a new database and back up the new database. If you can't BCP the data out of the database, you will have to restore it from the last valid backup. If you don't have a backup file, but do have a backup of an MDF file that is not corrupt, consider recovering the database from the MDF file. If you don't experience any errors with DBCC CHECKDB, you can put your database in multi-user mode and disallow updates to the system tables:Sp_configure 'allow updates', 0 RECONFIGURE WITH OVERRIDEALTER DATABASE pubs SET MULTI_USER DBCC STACKDUMP***This command initiates a stack dump - generating a file or series of files reflecting the current condition of the server. You can supply the user id or SPID of the connection you wish to troubleshoot, in which case DBCC STACKDUMP will generate a file with detailed information about the specified connection. Alternatively, you can execute the command with no parameters. The generated output file will be stored in the Microsoft SQL Server\MSSQL\LOG directory of the SQL Server installation and will have a name similar to the following: SQLDump002.txt. As you might imagine, a file containing detailed information about all current connections can be quite large - its size can be measured in megabytes. If you call Microsoft's tech support, you're very likely to be asked to execute this command and submit it along with your description of the problem. For example, the following command generates a stack dump for SPID 55:DBCC STACKDUMP(55) |