What are the DBCC Commands in SQL Server?

October 18, 2012

DBCC Commands in SQL Server are the Database Console Commands which are grouped into 4 categories:

  1. Maintenance
  2. Miscellaneous
  3. Informational
  4. Validation

1. Maintenance

  • DBCC CLEANTABLE
  • DBCC INDEXDEFRAG
  • DBCC DBREINDEX
  • DBCC SHRINKDATABASE
  • DBCC DROPCLEANBUFFERS
  • DBCC SHRINKFILE
  • DBCC FREEPROCCACHE
  • DBCC UPDATEUSAGE

2. Miscellaneous

  • DBCC dllname (FREE)
  • DBCC HELP
  • DBCC FREESESSIONCACHE
  • DBCC TRACEOFF
  • DBCC FREESYSTEMCACHE
  • DBCC TRACEON

3. Informational

  • DBCC INPUTBUFFER
  • DBCC SHOWCONTIG
  • DBCC OPENTRAN
  • DBCC SQLPERF
  • DBCC OUTPUTBUFFER
  • DBCC TRACESTATUS
  • DBCC PROCCACHE
  • DBCC USEROPTIONS
  • DBCC SHOW_STATISTICS

4.Validation

  • DBCC CHECKALLOC
  • DBCC CHECKFILEGROUP
  • DBCC CHECKCATALOG
  • DBCC CHECKIDENT
  • DBCC CHECKCONSTRAINTS
  • DBCC CHECKTABLE
  • DBCC CHECKDB
Advertisements

Space allocation in all the tables in a database

July 12, 2012

The below query batch will display the space used by all the tables in a database:

SET NOCOUNT ON

USE <DATABASE Name>

–The below DBCC command will correct the pages and row count inaccuracies in catalog views.
DBCC UPDATEUSAGE(0)

— The below command will display Database size.
EXEC sp_spaceused

— The below portion will display the space used by all the tables in the database
CREATE TABLE #temptable
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

INSERT #temptable EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

SELECT *
FROM   #temptable

— # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #temptable
 
DROP TABLE #temptable


%d bloggers like this: