DBCC stands for Database Consistency Checker. Version 6.x of SQL Server extended the scope of DBCC by introducing several new commands that can help a DBA probe into the inner workings of SQL Server. This appendix discusses the traditional DBCC commands along with the enhancements included in version 6.x.
DBCC commands are commonly used to perform the following tasks:
NOTE: The majority of the commands discussed in this appendix do not resolve errors--they only report that the error exists.
Table E.1 provides a quick syntax reference for DBCC commands.
Command | Notes |
DBCC CHECKALLOC [(database_name [, NOINDEX])] | Use NEWALLOC instead (5) |
DBCC CHECKCATALOG | |
[(database_name)] | |
DBCC CHECKTABLE(table_name [, NOINDEX | index_id] | Performance improved with version 6.x (4) |
DBCC CHECKDB[(database_name [, NOINDEX])] | Performance improved with version 6.x (4) |
DBCC CHECKIDENT[(table_name)] | (3) |
DBCC DBREINDEX ([[`db_name.username.table_name'[, ind_name[, fillfactor [, SORTED_DATA | SORTED_DATA_REORG]) | (1) |
DBCC DBREPAIR (database_name, DROPDB [, NOINIT]) | |
DBCC dllname (FREE) | |
DBCC INPUTBUFFER (spid) | (3) |
DBCC MEMUSAGE | |
DBCC NEWALLOC[(database_name [, NOINDEX])] | Replaces CHECKALLOC (3) |
DBCC OPENTRAN({database_name} | {database_id})[WITH TABLERESULTS] | (3) |
DBCC OUTPUTBUFFER (spid) | (3) |
DBCC PERFMON | (3) |
DBCC PINTABLE (database_id, table_id) | (3) |
DBCC ROWLOCK (dbid, tableid, set) | (1) |
DBCC SHOW_STATISTICS(table_name, index_name) | (3) |
DBCC SHOWCONTIG(table_id, [index_id]) | (3) |
DBCC SHRINKDB (database_name [, new_size[, `MASTEROVERRIDE']]) | (3) |
DBCC SQLPERF ({IOSTATS | LRUSTATS | NETSTATS| RASTATS [, CLEAR]} {THREADS} | {LOGSPACE}) | New counters with version 6.x (4) |
DBCC TEXTALL [({database_name | database_id}[, FULL | FAST])] | |
DBCC TEXTALLOC [({table_name | table_id}[, FULL | FAST])] | |
DBCC TRACEOFF (trace#) | (3) |
DBCC TRACEON (trace#) | (3) |
DBCC TRACESTATUS (trace# [, trace#...]) | (3) |
DBCC UNPINTABLE (database_id, table_id) | (3) |
DBCC UPDATEUSAGE ({0 | database_name}[, table_name [, index_id]]) | USEROPTIONS}[WITH COUNT_ROWS] | Version 6.5 syntax (2) |
DBCC UPDATEUSAGE ({0 | database_name}[, table_name [, index_id]]) | Version 6.0 syntax (3) |
DBCC USEROPTIONS | (3) |
The following abbreviations are used in Table E.1:
1 = New in version 6.5
2 = Improved in version 6.5
3 = Introduced in version 6.0
4 = Improved in version 6.0 5 = Use for backward compatibility (to versions before version 6.0)
TIP: When DBCC performance is a primary concern (especially when you are working with very large databases), use the NOINDEX argument with the following commands: CHECKALLOC, CHECKTABLE, CHECKDB, and NEWALLOC. When the NOINDEX argument is specified, only clustered indexes are inspected for errors. All nonclustered indexes are ignored. This option is generally safe to use because damaged indexes can be dropped and re-created without affecting the data within a table.
DBCC commands often generate a great amount of output. The problem is determining what is relevant within the output. Use the following list as a guide to what to look for in the DBCC output:
TIP: To help automate the process of detecting error messages in DBCC output, redirect the DBCC output to an ASCII text file and use the FINDSTR.EXE utility provided with Windows NT. Set up FINDSTR.EXE to look for keywords such as corrupt, error :, and so on.
When an error is reported by DBCC, you should immediately investigate it. Unresolved errors can propagate throughout a database, increasing the likelihood of permanent data corruption.
The following items provide a general guideline for investigating and resolving errors reported by DBCC:
You should run the commands listed in Table E.2 frequently. These commands can detect database and table corruption, along with structure inconsistencies. Table E.2. Essential DBCC commands.
Command |
DBCC CHECKDB |
DBCC CHECKTABLE |
DBCC NEWALLOC |
DBCC CHECKCATALOG |
Table E.3 details the scope of several DBCC commands, along with their performance and effectiveness.
The following DBCC commands are used for tables and databases: CHECKALLOC
CHECKCATALOG
CHECKTABLE
CHECKDB
CHECKIDENT
DBREINDEX
NEWALLOC
TEXTALL
TEXTALLOC
SHOWCONTIG
UPDATEUSAGE
Syntax:
DBCC CHECKALLOC [(database_name [, NOINDEX])]
CHECKALLOC is designed to be compatible with previous versions of SQL Server. In SQL Server 6.x, use NEWALLOC instead of CHECKALLOC. NEWALLOC provides greater detail and continues to process the remainder of the database after an error has been detected.
CHECKALLOC scans the database to ensure that page allocation is correct.
Syntax:
DBCC CHECKCATALOG [(database_name)]
CHECKCATALOG checks the system tables for consistency by ensuring that each datatype in the syscolumns table has a matching entry in the systypes table, that each table and view in the sysobjects table has one or more matching records in the syscolumns table, and that the last checkpoint in the syslogs table is correct. Segment information is also displayed.
Use this command before dumping the database or when you suspect corruption within the system tables.
Example:
DBCC CHECKCATALOG (pubs)
Sample Output:
The following segments have been defined for database
4 (database name pubs).
virtual start addr size segments
-------------------- ------ --------------------------
2052 512
0
1
2
10756 1024
0
1
2
Syntax:
DBCC CHECKTABLE (table_name [, NOINDEX | index_id]
CHECKTABLE ensures that all pointers are consistent, that data and index pages are properly linked, that indexes match the proper sort order, and that page offsets and page information are correct. Run this command when you suspect that a table is corrupt or as part of your periodic maintenance plan.
NOTE: When CHECKTABLE is run against the syslogs table, the amount of free space and remaining log space are also reported.
Example:
DBCC CHECKTABLE(titles)
Sample Output:
The total number of data pages in this table is 3.
Table has 18 data rows.
Example:
DBCC CHECKTABLE(syslogs)
Sample Output:
The total number of data pages in this table is 4389.
The number of rows in Sysindexes for this table was 198216. It has been corrected
to 198217.
*** NOTICE: Space used on the log segment is 8.78 Mbytes, 95.25.
*** NOTICE: Space free on the log segment is 0.44 Mbytes, 4.75.
Table has 198217 data rows.
Syntax:
DBCC CHECKDB [(database_name [, NOINDEX])]
CHECKDB checks all tables and indexes in a database for pointer and data page errors. This command may generate the following message:
The number of data pages in Sysindexes for this table
was 9. It has been corrected to 1.
The number of rows in Sysindexes for this table was 273. It has been corrected
to 16.
Do not be alarmed when you see this message. It means that SQL Server is performing some internal housekeeping to keep row counts accurate for the sp_spaceused command.
In terms of error checking, CHECKDB is the same as CHECKTABLE, except that CHECKDB inspects every table in the database as opposed to checking only a single table in the database.
CAUTION: Do not run DBCC CHECKDB while other users are in the database! This command should be run when the database is in single-user mode. Doing so ensures that the information reported by CHECKDB is accurate. Also, the number of locks generated by CHECKDB could lead to severe blocking and contention for resources if other users are in the database.
Example:
/* set database to single user */
sp_dboption pubs,'single user',TRUE
go
DBCC CHECKDB(pubs)
go
/* reset database option */
sp_dboption pubs,'single user',FALSE
go
Sample Output:
Checking 1
The total number of data pages in this table is 4.
Table has 69 data rows.
Checking 2
The total number of data pages in this table is 4.
Table has 49 data rows.
Checking 3
The total number of data pages in this table is 1.
The number of data pages in Sysindexes for this table was 9. It has been corrected
to 1.
The number of rows in Sysindexes for this table was 273. It has been corrected
to 16.
Syntax:
DBCC CHECKIDENT [(table_name)]
The CHECKIDENT command checks the IDENTITY datatype in a table. It returns the current identity value and the maximum identity value.
Example:
DBCC CHECKIDENT(jobs)
Sample Output:
Checking identity information: current identity value `14', maximum column value `14'.
Syntax:
DBCC DBREINDEX ([[`db_name.username.table_name'[, ind_name
[, fillfactor[, SORTED_DATA | SORTED_DATA_REORG]]]]]) [WITH NOINFOMSGS]
The DBREINDEX command rebuilds a table's indexes. When using PRIMARY KEY or UNIQUE constraints, the indexes used to support these constraints can be rebuilt without have to drop and re-create the constraints.
Example (rebuilds all indexes):
DBCC DBREINDEX (titleauthor,"")
Example (rebuilds a particular index):
DBCC DBREINDEX (titleauthor,"auidind")
Syntax:
DBCC NEWALLOC [(database_name [, NOINDEX])]
Introduced in SQL Server 6.0, NEWALLOC is the improved version of the CHECKALLOC command. NEWALLOC provides greater detail than CHECKALLOC and continues to process the remainder of the database after an error has been detected (unlike CHECKALLOC, which stops processing when an error has been detected).
NEWALLOC scans the data and index pages for extent structure errors. It ensures that page allocation is correct and that all allocated pages are in use.
CAUTION: Do not run DBCC NEWALLOC while other users are in the database! This command should be run when the database is in single-user mode to ensure that the information reported by NEWALLOC is accurate.
Example:
/* set database to single user */
sp_dboption pubs,'single user',TRUE
go
DBCC NEWALLOC(pubs)
go
/* reset database option */
sp_dboption pubs,'single user',FALSE
go
Sample Output:
Checking pubs
***************************************************************
TABLE: sysobjects OBJID = 1
INDID=1 FIRST=1 ROOT=8 DPAGES=4 SORT=0
Data level: 1. 4 Data Pages in 1 extents.
Indid : 1. 1 Index Pages in 1 extents.
INDID=2 FIRST=40 ROOT=41 DPAGES=1 SORT=1
Indid : 2. 3 Index Pages in 1 extents.
TOTAL # of extents = 3
***************************************************************
TABLE: sysindexes OBJID = 2
INDID=1 FIRST=24 ROOT=32 DPAGES=4 SORT=0
Data level: 1. 4 Data Pages in 1 extents.
Indid : 1. 1 Index Pages in 1 extents.
TOTAL # of extents = 2
***************************************************************
TABLE: jobs OBJID = 592005140
INDID=1 FIRST=496 ROOT=520 DPAGES=1 SORT=0
Data level: 1. 1 Data Pages in 1 extents.
Indid : 1. 2 Index Pages in 1 extents.
TOTAL # of extents = 2
***************************************************************
TABLE: employee OBJID = 688005482
INDID=1 FIRST=648 ROOT=656 DPAGES=2 SORT=1
Data level: 1. 2 Data Pages in 1 extents.
Indid : 1. 2 Index Pages in 1 extents.
INDID=2 FIRST=664 ROOT=664 DPAGES=1 SORT=1
Indid : 2. 2 Index Pages in 1 extents.
TOTAL # of extents = 3
***************************************************************
TABLE: pub_info OBJID = 864006109
INDID=1 FIRST=568 ROOT=584 DPAGES=1 SORT=0
Data level: 1. 1 Data Pages in 1 extents.
Indid : 1. 2 Index Pages in 1 extents.
INDID=255 FIRST=560 ROOT=608 DPAGES=0 SORT=0
TOTAL # of extents = 2
***************************************************************
Processed 49 entries in the Sysindexes for dbid 4.
Alloc page 0 (# of extent=32 used pages=57 ref pages=57)
Alloc page 256 (# of extent=26 used pages=35 ref pages=35)
Alloc page 512 (# of extent=15 used pages=38 ref pages=38)
Alloc page 768 (# of extent=1 used pages=1 ref pages=1)
Alloc page 1024 (# of extent=2 used pages=9 ref pages=2)
Alloc page 1280 (# of extent=1 used pages=1 ref pages=1)
Total (# of extent=77 used pages=141 ref pages=134) in this database
DBCC execution completed. If DBCC printed error messages, see your System
Administrator.
Syntax:
DBCC TEXTALL [({database_name | database_id}[, FULL | FAST])]
TEXTALL checks the allocation of TEXT and IMAGE columns for all tables in a database that contain TEXT or IMAGE columns. The FULL option generates a complete allocation report; the FAST option does not generate an allocation report. FULL is the default report option.
Example:
DBCC TEXTALL (pubs, FULL)
Sample Output:
***************************************************************
TABLE: sysarticles OBJID = 16
INDID=255 FIRST=328 ROOT=328 DPAGES=0 SORT=0
Data level: 1. 1 Data Pages in 0 extents.
Indid : 255. 0 Index Pages in 0 extents.
***************************************************************
TABLE: pub_info OBJID = 864006109
INDID=255 FIRST=560 ROOT=608 DPAGES=0 SORT=0
Data level: 1. 1 Data Pages in 0 extents.
Indid : 255. 73 Index Pages in 0 extents.
Syntax:
DBCC TEXTALLOC [({table_name | table_id}[, FULL | FAST])]
TEXTALLOC checks a specified table for TEXT or IMAGE allocation errors. The FULL option generates a complete allocation report; the FAST option does not generate an allocation report. FULL is the default report option.
Example:
DBCC TEXTALLOC (pub_info, FULL)
Sample Output:
***************************************************************
TABLE: pub_info OBJID = 864006109
INDID=255 FIRST=560 ROOT=608 DPAGES=0 SORT=0
Data level: 1. 1 Data Pages in 0 extents.
Indid : 255. 73 Index Pages in 0 extents.
Syntax:
DBCC SHOWCONTIG (table_id, [index_id])
SHOWCONTIG determines the amount of table fragmentation. A high degree of fragmentation can lead to poor query performance because more data pages must read by SQL Server to process a query.
Fragmentation occurs when modification statements (DELETE, INSERT, and UPDATE) are performed on a table. A table subject to a lot of modification statements is more likely to become fragmented than a table that is seldom modified.
To determine the degree of fragmentation, inspect the Scan Density, Avg. Page density, and Avg. Overflow Page density values generated by the SHOWCONTIG command. A Scan Density value less than 100% indicates that some fragmentation exists.
To defragment a table, drop and re-create the table's clustered index or
use DBCC DBREINDEX with the SORTED_DATA_REORG option
or
use BCP to copy out the data, drop the table, re-create the table, and use BCP again
to copy in the data.
TIP: Use the object_id() function to determine a table's ID.
Example:
/* get table id */
SELECT object_id(`sample_table')
-----------
96003373
/* run DBCC command */
DBCC SHOWCONTIG(96003373)
Sample Output:
TablJ `sample_table' (96003373) Indid: 0 dbid:6
TABLE level scan performed.
- Pages Scanned................................: 4096
- Extent Switches..............................: 514
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.42% [512:515]
- Avg. Bytes free per page.....................: 89.0
- Avg. Page density (full).....................: 95.58%
- Overflow Pages...............................: 4095
- Avg. Bytes free per Overflow page............: 89.0
- Avg. Overflow Page density...................: 95.6%
- Disconnected Overflow Pages..................: 0
Version 6.5 Syntax:
DBCC UPDATEUSAGE ({0 | database_name} [, table_name
[, index_id]])
{USEROPTIONS}[WITH COUNT_ROWS]
Version 6.0 Syntax:
DBCC UPDATEUSAGE ({0 | database_name} [, table_name [, index_id]])
Whenever an index is dropped from a table, sp_spaceused inaccurately reports space utilization. Use the UPDATEUSAGE command to correct the inaccuracy.
CAUTION: The output from the system procedure sp_spaceused should be used only as estimate for space utilization. When an index is dropped, the information returned from sp_spaceused is inaccurate until the UDPATEUSAGE command is executed or until the table is dropped and
re-created.
Example:
DBCC UPDATEUSAGE(`sales','sample_table')
Sample Output:
DBCC UPDATEUSAGE: Sysindexes row for Table `sample_table'
(IndexId=0) updated:
RSVD Pages: Changed from (4103) to (4120) pages
The DBREPAIR command is used to drop a damaged database.
Syntax:
DBCC DBREPAIR (database_name, DROPDB [, NOINIT])
CAUTION: DBREPAIR does not repair a corrupt database! Instead, it drops a corrupt database.
In SQL Server 6.x, you can drop a corrupt database with the DROP DATABASE command. In previous versions of SQL Server, you must use the DBREPAIR command to drop a damaged database. If you are unable to drop a database with the DROP DATABASE command, use the system procedure sp_dbremove to drop a damaged database.
The following two DBCC commands return process information.
Syntax:
DBCC INPUTBUFFER (spid)
DBCC OUTPUTBUFFER (spid)
The INPUTBUFFER and OUTPUTBUFFER commands allow a DBA to monitor process activity. The INPUTBUFFER command displays the command last executed by a process; the OUTPUTBUFFER command displays the corresponding result. Unfortunately, the information returned from the OUTPUTBUFFER command can be difficult to understand because it is displayed in hexadecimal and ASCII text.
TIP: Use the system procedure sp_who to determine the spid of a process. Use the INPUTBUFFER command to diagnose blocking and resource utilization problems. When performance begins to suffer, look for data modification queries that do not contain WHERE clauses or SELECT queries that perform table scans on large tables.
Example:
DBCC INPUTBUFFER(11)
Sample Output:
Input Buffer
-------------
select *
from authors
The following two DBCC commands return Performance Monitor statistics.
Syntax:
DBCC PERFMON
DBCC SQLPERF ({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]}
{THREADS} | {LOGSPACE})
The PERFMON command combines the different components of the SQLPERF command (IOSTATS, LRUSTATS, and NETSTATS) into a single DBCC statement.
Example:
DBCC PERFMON
Sample Output:
Statistic Value
-------------------------------- ------------------------
Log Flush Requests 110.0
Log Logical Page IO 112.0
Log Physical IO 94.0
Log Flush Average 1.17021
Log Logical IO Average 1.19149
Batch Writes 68.0
Batch Average Size 2.72
Batch Max Size 8.0
Page Reads 643.0
Single Page Writes 132.0
Reads Outstanding 0.0
Writes Outstanding 0.0
Transactions 92.0
Transactions/Log Write 0.978723
The following commands are used to turn on and off trace flags and to check the status of a trace flag.
Syntax:
DBCC TRACEOFF (trace#)
DBCC TRACEON (trace#)
DBCC TRACESTATUS (trace# [, trace#...])
TIP: The trace flag 1200 can be useful for tracking locking behavior. You must also turn on trace flag 3604 to echo trace information to the client workstation (see SQL Server's Books Online for a complete list of trace flags).
Example:
DBCC traceon(3604)
DBCC traceon(1200)
UPDATE t_1
SET c_1 = 0
Sample Output from DBCC Trace Flag 1200:
Process 11 requesting page lock of type SH_PAGE on 7
25
Process 11 releasing page lock of type SH_PAGE on 7 25
...
Process 11 releasing page lock of type SH_PAGE on 7 26
Process 11 requesting table lock of type EX_TAB on 7 80003316
...
Process 11 clearing all pss locks
The following sections describe the DBCC commands you can use for tracking memory and data cache information.
Syntax:
DBCC MEMUSAGE
The MEMUSAGE command displays memory usage, buffer cache, and procedure cache information.
Example:
DBCC MEMUSAGE
Sample Output:
Memory Usage:
Meg. 2K Blks Bytes
Configured Memory: 8.0000 4096 8388608
Code size: 1.7166 879 1800000
Static Structures: 0.2385 123 250064
Locks: 0.2861 147 300000
Open Objects: 0.1144 59 120000
Open Databases: 0.0031 2 3220
User Context Areas: 0.7505 385 787002
Page Cache: 3.3269 1704 3488480
Proc Headers: 0.0800 41 83936
Proc Cache Bufs: 1.3457 689 1411072
Buffer Cache, Top 20:
DB Id Object Id Index Id 2K Buffers
1 5 0 96
1 3 0 41
1 1 0 21
1 1 2 8
1 99 0 8
2 3 0 8
1 2 0 6
1 5 1 6
1 6 0 4
1 36 0 4
1 6 1 3
2 2 0 3
1 45 255 2
1 704005539 1 2
2 99 0 2
3 2 0 2
3 8 0 2
4 1 2 2
4 2 0 2
5 1 2 2
Procedure Cache, Top 9:
Procedure Name: sp_MSdbuserprofile
Database Id: 1
Object Id: 1449056198
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 2
Size of plans: 0.171600 Mb, 179936.000000 bytes, 90 pages
Procedure Name: sp_helpdistributor
Database Id: 1
Object Id: 1372531923
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 2
Size of plans: 0.042969 Mb, 45056.000000 bytes, 24 pages
Procedure Name: sp_server_info
Database Id: 1
Object Id: 361052322
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 2
Size of plans: 0.006332 Mb, 6640.000000 bytes, 4 pages
Procedure Name: sp_MSSQLOLE65_version
Database Id: 1
Object Id: 1481056312
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 1
Size of plans: 0.001543 Mb, 1618.000000 bytes, 1 pages
Procedure Name: sp_sqlregister
Database Id: 1
Object Id: 985054545
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 1
Size of plans: 0.000822 Mb, 862.000000 bytes, 1 pages
Procedure Name: xp_msver
Database Id: 1
Object Id: 1036530726
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 1
Size of plans: 0.000578 Mb, 606.000000 bytes, 1 pages
Procedure Name: xp_sqlregister
Database Id: 1
Object Id: 953054431
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 1
Size of plans: 0.000578 Mb, 606.000000 bytes, 1 pages
Procedure Name: xp_snmp_getstate
Database Id: 1
Object Id: 921054317
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 1
Size of plans: 0.000578 Mb, 606.000000 bytes, 1 pages
Procedure Name: xp_regread
Database Id: 1
Object Id: 585053120
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 1
Size of plans: 0.000578 Mb, 606.000000 bytes, 1 pages
DBCC execution completed. If DBCC printed error messages, see your System
Administrator.
Syntax:
DBCC PINTABLE (database_id, table_id)
DBCC UNPINTABLE (database_id, table_id)
The PINTABLE command forces a table to remain in cache until it is removed from the cache with the UNPINTABLE command. You should be careful when pinning a table in the cache. By keeping a table constantly in the cache, you can improve data access performance. However, a large table can dominate the data cache. This could reduce the amount of data held in cache for other tables, thus hindering performance.
Example:
declare @id integer
select @id = object_id(`authors')
/* 4 = pubs database */
DBCC PINTABLE (4,@id)
Sample Output:
WARNING: Pinning tables should be carefully considered.
If a pinned table is larger or grows larger than the available data cache, the server
may need to be restarted and the table unpinned.
DBCC execution completed. If DBCC printed error messages, see your System
Administrator.
Use the OPENTRAN command for information about transactions.
Syntax:
DBCC OPENTRAN ({database_name} | {database_id}) [WITH TABLERESULTS]
The OPENTRAN command reports the oldest open transaction. An open transaction can stem from an aborted transaction, a runaway transaction, or poor transaction management. If necessary, you can terminate the offending transaction by issuing the KILL command with the process ID returned from the DBCC OPENTRAN command.
TIP: Long-running transactions can lead to contention for resources, which can lead to blocking. Use DBCC OPENTRAN to detect open transactions. If necessary, use the KILL command to cancel the transaction.
Example:
DBCC OPENTRAN(pubs)
Sample Output:
Transaction Information for database: pubs
Oldest active transaction:
SPID : 12
UID : 1
SUID : 1
Name : del
RID : (14653 , 30)
Time Stamp : 0001 0003CB68
Start Time : Sep 20 1995 9:30:41:690PM
The following sections cover some of the other types of DBCC commands.
Syntax:
DBCC ROWLOCK (dbid, tableid, set)
ROWLOCK allows you to dynamically manage insert row-level locking for a specific table.
Example:
declare @id int
select @id = object_id(`authors')
/* 4 = pubs database */
DBCC ROWLOCK (4, @id, 1)
Sample Output:
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, see your System
Administrator.
Syntax:
DBCC SHOW_STATISTICS (table_name, index_name)
SHOW_STATISTICS displays index distribution information.
Example:
DBCC SHOW_STATISTICS (`authors','aunmind')
Sample Output:
Updated Rows Steps Density
-------------------- ----------- ----------- ------------------------
Apr 3 1996 3:46AM 23 22 0.0396975
(1 row(s) affected)
All density Columns
------------------------ ------------------------------
0.047259 au_lname
0.0434783 au_lname, au_fname
Syntax:
DBCC SHRINKDB (database_name [, new_size [, `MASTEROVERRIDE']])
NOTE: The size is specified in 2K pages.
SHRINKDB has two purposes:
Example:
DBCC SHRINKDB (sales , 5376)
Sample Output:
DBCC execution completed. If DBCC printed error messages,
see your System
Administrator.
Syntax:
DBCC USEROPTIONS
The USEROPTIONS command displays the status of SET commands for the current session.
Example:
DBCC USEROPTIONS
Sample Output:
Set Option Value
------------------------------ ----------------------------------------------
textsize 64512
language us_english
dateformat mdy
datefirst 7
Syntax:
DBCC dllname (FREE)
This command removes a DLL (dynamic link library) from memory.
To order books from QUE, call us at 800-716-0044
or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster
Company.