SQL SERVER – Types of DBCC Commands When Used as Database Console Commands

Just a day ago, while discussing some SQL issues with one of the Sr. Database Administrator in India, we end up discussing DBCC as Database Console Commands when used as T-SQL. We both tried to remember what are the types of DBCC as Database Console Commands and could not come up with more than two types, however we both knew there are four.

When the conversation was over, I looked up MSDN for the types of the DBCC. I found following documentation here. There are four types of the Database Console Commands.

Maintenance

Maintenance tasks on a database, index, or filegroup.

Examples:

DBCC INPUTBUFFER

DBCC SHOWCONTIG

Miscellaneous

Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

Examples:

DBCC CHECKCATALOG

DBCC CHECKIDENT

Informational

Tasks that gather and display various types of information.

Examples:

DBCC DROPCLEANBUFFERS

DBCC SHRINKFILE

Validation

Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

Examples:

DBCC dllname (FREE)

DBCC TRACEOFF

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL – DBCC

SQL SERVER – 2005 – Find Database Status Using sys.databases or DATABASEPROPERTYEX

While writing article about database collation, I came across sys.databases and DATABASEPROPERTYEX. It was very interesting to me that this two can tell user so much about database properties.

Following are main database status: (Reference: BOL Database Status)

ONLINE
Database is available for access.

OFFLINE
Database is unavailable.

RESTORING
One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline.

RECOVERING
Database is being recovered.

RECOVERY PENDING
SQL Server has encountered a resource-related error during recovery.

SUSPECT
At least the primary filegroup is suspect and may be damaged.

EMERGENCY
User has changed the database and set the status to EMERGENCY.

Let us see how we can find out database status using this sys.databases and DATABASEPROPERTYEX.

1) Using T-SQL (My Recommendation)

Example:
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status')
DatabaseStatus_DATABASEPROPERTYEX
GO
SELECT state_desc DatabaseStatus_sysDatabase
FROM sys.databases
WHERE name = 'AdventureWorks'
GO

ResultSet:
DatabaseStatus_DATABASEPROPERTYEX
——————————————————
ONLINE

DatabaseStatus_sysDatabase
——————————————————
ONLINE

2) Using SQL Server Management Studio


Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL Database Status, BOL sys.databases, BOL DATABASEPROPERTYEX

SQL SERVER – 2005 – Explanation and Script for Online Index Operations – Create, Rebuild, Drop

SQL Server 2005 Enterprise Edition supports online index operations. Index operations are creating, rebuilding and dropping indexes.

The question which I receive quite often – what is online operation? Is online operation is related to web, internet or local network?
Online operation means when online operations are happening the database are in normal operational condition, the processes which are participating in online operations does not require exclusive access to database. In case of Online Indexing Operations, when Index operations (create, rebuild, dropping) are occuring they do not require exclusive access to database, they do not lock any database tables. This is major important upgrade in SQL Server from previous versions.

Previous versions of SQL Server required exclusive access to database, and database table were not accessible by other normal processes. This is the reason of time-outs and lock-outs when index operations are performed on SQL Server. I have seen many times major performance issues with larger databases, as due to business requirement and rules it has to be online all the time and index operations were not performed on them due to this concurrent accesses (normal processes vs prioritized index operations) conflict issues. This requirement of highly-availability application is very well addressed in SQL Server 2005 by adding new feature of Online Index Operations.

Syntax of Online Index is very simple. When the index on database is created, ONLINE = ON clause is added to enable online operations on index. ONLINE = ON must be specified to have this feature enabled. The default value of ONLINE option is OFF. The example is taken from BOL.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD
WITH (ONLINE = ON);

Online index operations occurs in three steps.

1) Preparation – Snapshot of original index structure, known as source index, is taken and copied at other place. (concurrent activity suspended for the moment)

2) Build – New operations are executed on this copy, which generates new required index known as target index.

3) Final – Original source index is replaced by new target index. (concurrent activity suspended for the moment)

ALTER INDEX REORGANIZE statement is always performed online, so it is highly recommended to use this new feature over DBCC INDEXDEFRAG of previous version. (Read Complete Article : SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005) DBCC INDEXDEFRAG may be deprecated in future versions of SQL Server so not recommended to use it. Creating partition index online is possible but altering partition index is not possible online.

Additional Notes:
If any of the column in Included Index (Read Complete Article : SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index) are VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX), they must be created with ONLINE = OFF options.
XML datatypes index can not be build online.
Initial clustered index on view and disabled clustered index can not be build online.

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL – Alter Index, BOL – Create Index

SQL SERVER – Fix : Error 2596 The repair statement was not processed. The database cannot be in read-only mode

ERROR 2596 : The repair statement was not processed. The database cannot be in read-only mode.

This error is described little confusing. I have received quite a few email asking why this error happens when the database is already in read-only mode.

Fix/Solution/Workaround :
Yes, This error happens when the database is in read only mode. To repair the database using DBCC command it should not be in repair mode. It should be in read-write mode before it is repaired.
USE MASTER;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH
ROLLBACK
IMMEDIATE;
GO
ALTER DATABASE AdventureWorks
SET READ_WRITE;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL – ALTER DATABASE

SQL SERVER – 2005 – SSMS – View/Send Query Results to Text/Grid/Files

Many times I have been asked how to change the result window from Text to Grid and vice versa. There are three different ways to do it.

Method 1 : Key-Board Short Cut
Results to Text – CTRL + T
Results to Grid – CTRL + D
Results to File – CTRL + SHIFT + F

Method 2 : Using Toolbar

Method 3 : Using Menubar

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

Index Fragmentation:
When a page of data fills to 100 percent and more data must be added to it, a page split occurs. To make room for the new data, SQL Server must move half of the data from the full page to a new page. The new page that is created is created after all the pages in database. Therefore, instead of going right from one page to the next when looking for data, SQL Server has to go one page to another page around the database looking for the next page it needs. This is Index Fragmentation. Severity of the Index fragmentation can be determined by querying sys.DM_DB_INDEX_PHYSICAL_STATS.

SQL SERVER 2000:
DBCC SHOWCONTIG was used to find index fragmentation. In SQL SERVER 2005 it is deprecated and replaced by query to sys.DM_DB_INDEX_PHYSICAL_STATS.

SQL SERVER 2005:
SELECT query to sys.DM_DB_INDEX_PHYSICAL_STATS displays all the Index Fragmentation related information.

Examples to determine Index Fragmentation in SQL SERVER 2005:
To return the Index Information for only Sales.SalesOrderDetail Table:
USE AdventureWorks;
SELECT INDEX_ID, AVG_FRAGMENTATION_IN_PERCENT
FROM sys.dm_db_index_physical_stats
(DB_ID(),OBJECT_ID(N'Sales.SalesOrderDetail'), NULL, NULL, 'DETAILED')

To return all the information for all the Indexes in Database:
SELECT *
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);

Index De-Fragmentation and Index Rebuilding:
Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. I prefer Index Rebuilding is required if Index is fragmented more than 10%. Reorganizing an index de-fragments the leaf level of clustered and non-clustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance.

SQL SERVER 2000:
The DBCC INDEXDEFRAG and DBCC DBREINDEX statements are used to reduce table fragmentation.

SQL SERVER 2005:
ALTER INDEX with the REBUILD clause. This statement replaces the DBCC DBREINDEX statement of SQL SERVER 2000. (DBCC DBREINDEX can still be used in SQL SERVER 2005 but will be sure deprecated in future version of SQL SERVER. I do not recommend to continue use of this in SQL SERVER 2005)
CREATE INDEX with the DROP_EXISTING clause.
Both the above method perform the same function.

Examples to rebuild Index in SQL SERVER 2005:
To Rebuild only one Index:
USE AdventureWorks
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID
ON Production.ProductPhoto
REORGANIZE
GO

TO Rebuild all the Indexes on Table with Specifying options:
USE AdventureWorks
GO
ALTER INDEX ALL ON Production.Product
REBUILD
WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
GO

I have been asked following two questions many times:
1) What is difference between FILLFACTOR = 0 AND FILLFACTOR = 100?
They are SAME.
2) What FILLFACTOR my own Database Servers have for Index? Why?
90. It works great for me.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

SQL SERVER – Trace Flags – DBCC TRACEON

Trace flags are valuable tools as they allow DBA to enable or disable a database function temporarily. Once a trace flag is turned on, it remains on until either manually turned off or SQL Server restarted. Only users in the sysadmin fixed server role can turn on trace flags.

If you want to enable/disable Detailed Deadlock Information (1205), use Query Analyzer and DBCC TRACEON to turn it on.
1205 trace flag sends detailed information about the deadlock to the error log.

Enable Trace at current connection level:
DBCC TRACEON(1205)

Disable Trace:
DBCC TRACEOFF(1205)

Enable Multiple Trace at same time separating each trace with a comma.
DBCC TRACEON(1205,2528)

Disable Multiple Trace at same time separating each trace with a comma.
DBCC TRACEOFF(1205,2528)

To set the trace using the DBCC TRACEON command at a server level, Pass second argument to the function as -1.
DBCC TRACEON (1205, -1)

To enumerate a complete list of traces that are on run following command in query analyzer.
DBCC TRACESTATUS(-1)

SQL Server 2005 Trace Flags.

SQL Server 2000 SP3 Trace Flags. (Undocumented trace flags are not included in document)

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Disable Index – Enable Index – ALTER Index

There are few requirements in real world when Index on table needs to be disabled and re-enabled afterwards. e.g. DTS, BCP, BULK INSERT etc. Index can be dropped and recreated. I prefer to disable the Index if I am going to re-enable it again.

USE AdventureWorks
GO
----Diable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE
GO
----Enable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – DBCC commands List – documented and undocumented

Database Consistency Checker (DBCC) commands can gives valuable insight into what’s going on inside SQL Server system. DBCC commands have powerful documented functions and many undocumented capabilities. Current DBCC commands are most useful for performance and troubleshooting exercises.
To learn about all the DBCC commands run following script in query analyzer.
DBCC TRACEON(2520)
DBCC HELP (‘?’)
GO
To learn about syntax of an individual DBCC command run following script in query analyzer.
DBCC HELP(<command>)
GO
Following is the list of all the DBCC commands and their syntax. List contains all documented and undocumented DBCC commands.
DBCC activecursors [(spid)]

DBCC addextendedproc (function_name, dll_name)

DBCC addinstance (objectname, instancename)

DBCC adduserobject (name)

DBCC auditevent (eventclass, eventsubclass, success, loginname
, rolename, dbusername, loginid)

DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])

DBCC balancefactor (variance_percent)

DBCC bufcount [(number_of_buffers)]

DBCC buffer ( {‘dbname’ | dbid} [, objid [, number [, printopt={0|1|2} ]
[, dirty | io | kept | rlock | ioerr | hashed ]]])

DBCC bytes ( startaddress, length )

DBCC cachestats

DBCC callfulltext

DBCC checkalloc [(‘database_name'[, NOINDEX | REPAIR])]
[WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]

DBCC checkcatalog [(‘database_name’)] [WITH NO_INFOMSGS]

DBCC checkconstraints [( ‘tab_name’ | tab_id | ‘constraint_name’ | constraint_id )]
[WITH ALL_CONSTRAINTS | ALL_ERRORMSGS]

DBCC checkdb [(‘database_name'[, NOINDEX | REPAIR])]
[WITH NO_INFOMSGS[, ALL_ERRORMSGS]
[, PHYSICAL_ONLY][, ESTIMATEONLY][,DBCC TABLOCK]

DBCC checkdbts (dbid, newTimestamp)]

DBCC checkfilegroup [( [ {‘filegroup_name’ | filegroup_id} ]
[, NOINDEX] )] [WITH NO_INFOMSGS
[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]

DBCC checkident (‘table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ] )

DBCC checkprimaryfile ( {‘FileName’} [, opt={0|1|2|3} ])

DBCC checktable (‘table_name'[, {NOINDEX | index_id | REPAIR}])
[WITH NO_INFOMSGS[, ALL_ERRORMSGS]
[, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]

DBCC cleantable (‘database_name’|database_id, ‘table_name’|table_id,[batch_size])

DBCC cacheprofile [( {actionid} [, bucketid])

DBCC clearspacecaches (‘database_name’|database_id,
‘table_name’|table_id, ‘index_name’|index_id)

DBCC collectstats (on | off)

DBCC concurrencyviolation (reset | display | startlog | stoplog)

DBCC config

DBCC cursorstats ([spid [,’clear’]])

DBCC dbinfo [(‘dbname’)]

DBCC dbrecover (dbname [, IgnoreErrors])

DBCC dbreindex (‘table_name’ [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]

DBCC dbreindexall (db_name/db_id, type_bitmap)

DBCC dbrepair (‘dbname’, DROPDB [, NOINIT])

DBCC dbtable [({‘dbname’ | dbid})]

DBCC debugbreak

DBCC deleteinstance (objectname, instancename)

DBCC des [( {‘dbname’ | dbid} [, {‘objname’ | objid} ])]

DBCC detachdb [( ‘dbname’ )]

DBCC dropcleanbuffers

DBCC dropextendedproc (function_name)

DBCC dropuserobject (‘object_name’)

DBCC dumptrigger ({‘BREAK’, {0 | 1}} | ‘DISPLAY’ | {‘SET’, exception_number}
| {‘CLEAR’, exception_number})

DBCC errorlog

DBCC extentinfo [({‘database_name’| dbid | 0}
[,{‘table_name’ | table_id} [, {‘index_name’ | index_id | -1}]])]

DBCC fileheader [( {‘dbname’ | dbid} [, fileid])

DBCC fixallocation [({‘ADD’ | ‘REMOVE’},
{‘PAGE’ | ‘SINGLEPAGE’ | ‘EXTENT’ | ‘MIXEDEXTENT’}
, filenum, pagenum [, objectid, indid])

DBCC flush (‘data’ | ‘log’, dbid)

DBCC flushprocindb (database)

DBCC free dll_name (FREE)

DBCC freeproccache

dbcc freeze_io (db)

dbcc getvalue (name)

dbcc icecapquery (‘dbname’, stored_proc_name
[, #_times_to_icecap (-1 infinite, 0 turns off)])
Use ‘dbcc icecapquery (printlist)’ to see list of SP’s to profile.
Use ‘dbcc icecapquery (icecapall)’ to profile all SP’s.

dbcc incrementinstance (objectname, countername, instancename, value)

dbcc ind ( { ‘dbname’ | dbid }, { ‘objname’ | objid }, { indid | 0 | -1 | -2 } )

DBCC indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid |indname})

DBCC inputbuffer (spid)

DBCC invalidate_textptr (textptr)

DBCC invalidate_textptr_objid (objid)

DBCC iotrace ( { ‘dbname’ | dbid | 0 | -1 }
, { fileid | 0 }, bufsize, [ { numIOs | -1 }
[, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )

DBCC latch ( address [, ‘owners’] [, ‘stackdumps’])

DBCC lock ([{‘DUMPTABLE’ | ‘DUMPSTATS’ | ‘RESETSTATS’ | ‘HASH’}] |
[{‘STALLREPORTTHESHOLD’, stallthreshold}])

DBCC lockobjectschema (‘object_name’)

DBCC log ([dbid[,{0|1|2|3|4}[,[‘lsn’,'[0x]x:y:z’]|[‘numrecs’,num]|[‘xdesid’,’x:y’]
|[‘extent’,’x:y’]|[‘pageid’,’x:y’]|[‘objid’,{x,’y’}]|[‘logrecs’,
{‘lop’|op}…]|[‘output’,x,[‘filename’,’x’]]…]]])

DBCC loginfo [({‘database_name’ | dbid})]

DBCC matview ({‘PERSIST’ | ‘ENDPERSIST’ | ‘FREE’ | ‘USE’ | ‘ENDUSE’})

DBCC memobjlist [(memory object)]

DBCC memorymap

DBCC memorystatus

DBCC memospy

DBCC memusage ([IDS | NAMES], [Number of rows to output])

DBCC monitorevents (‘sink’ [, ‘filter-expression’])

DBCC newalloc – please use checkalloc instead

DBCC no_textptr (table_id , max_inline)

DBCC opentran [({‘dbname’| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]

DBCC outputbuffer (spid)

DBCC page ( {‘dbname’ | dbid}, filenum, pagenum
[, printopt={0|1|2|3} ][, cache={0|1} ])

DBCC perflog

DBCC perfmon

DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}
, targetfile, targetpg, order={1|0})

DBCC pintable (database_id, table_id)

DBCC procbuf [({‘dbname’ | dbid}[, {‘objname’ | objid}
[, nbufs[, printopt = { 0 | 1 } ]]] )]

DBCC proccache

DBCC prtipage (dbid, objid, indexid [, [{{level, 0}
| {filenum, pagenum}}] [,printopt]])

DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]

DBCC readpage ({ dbid, ‘dbname’ }, fileid, pageid
, formatstr [, printopt = { 0 | 1} ])

DBCC rebuild_log (dbname [, filename])

DBCC renamecolumn (object_name, old_name, new_name)

DBCC resource

DBCC row_lock (dbid, tableid, set) – Not Needed

DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)

DBCC ruleon ( rulenum | rulestring } [, { rulenum | rulestring } ]+)

DBCC setcpuweight (weight)

DBCC setinstance (objectname, countername, instancename, value)

DBCC setioweight (weight)

DBCC show_statistics (‘table_name’, ‘target_name’)

DBCC showcontig (table_id | table_name [, index_id | index_name]
[WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]])

DBCC showdbaffinity

DBCC showfilestats [(file_num)]

DBCC showoffrules

DBCC showonrules

DBCC showtableaffinity (table)

DBCC showtext (‘dbname’, {textpointer | {fileid, pageid, slotid[,option]}})

DBCC showweights

DBCC shrinkdatabase ({dbid | ‘dbname’}, [freespace_percentage
[, {NOTRUNCATE | TRUNCATEONLY}]])

DBCC shrinkfile ({fileid | ‘filename’}, [compress_size
[, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])

DBCC sqlmgrstats

DBCC sqlperf (LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]}
| {THREADS} | {LOGSPACE})

DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, ‘THREADID’}] )]

DBCC tab ( dbid, objid )

DBCC tape_control {‘query’ | ‘release’}[,(‘.tape’)]

DBCC tec [( uid[, spid[, ecid]] )]

DBCC textall [({‘database_name’|database_id}[, ‘FULL’ | FAST] )]

DBCC textalloc ({‘table_name’|table_id}[, ‘FULL’ | FAST])

DBCC thaw_io (db)

DBCC traceoff [( tracenum [, tracenum … ] )]

DBCC traceon [( tracenum [, tracenum … ] )]

DBCC tracestatus (trace# [, …trace#])

DBCC unpintable (dbid, table_id)

DBCC updateusage ({‘database_name’| 0} [, ‘table_name’ [, index_id]])
[WITH [NO_INFOMSGS] [,] COUNT_ROWS]

DBCC upgradedb (db) DBCC usagegovernor (command, value)

DBCC useplan [(number_of_plan)]

DBCC useroptions DBCC wakeup (spid)

DBCC writepage ({ dbid, ‘dbname’ }, fileid, pageid, offset, length, data)

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL, Extreme Expert

SQL SERVER – DBCC commands to free several SQL Server memory caches

DBCC commands to free several SQL Server memory caches:

DBCC FREESYSTEMCACHE
Releases all unused cache entries from all caches. The SQL Server 2005 Database Engine proactively cleans up unused cache entries in the background to make memory available for current entries.

DBCC FREESESSIONCACHE
Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.

DBCC FREEPROCCACHE
Removes all elements from the procedure cache.

Reference : Pinal Dave (http://blog.SQLAuthority.com) ,BOL