Sunday, August 1, 2010

DBCC

Database Console Command


Command category
Perform
Maintenance
Maintenance tasks on a database, index, or filegroup.
Miscellaneous
Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
Informational
Tasks that gather and display various types of information.
Validation
Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

DBCC Command
Description
DBCC SHRINKDATABASE (DBName,20)
Shrinks database files. 20% will be free in each data page
DBCC FREEPROCCACHE
Removes the query plan so that all SPs will be recompiled
DBCC DBREINDEX
(TableName, IndexName)
Rebuilds the index
DBCC INDEXDEFRAG
(DatabaseName, TableName, IndexName)
Defrags the leaf level of the index and rearranges all the pages to match physical and logical order same to improve the performance
DBCC CHECKIDENT(TableName, NORESEED)
Gives the current Identity value. If 50, new record will have 51
DBCC CHECKIDENT
(TableName,RESEED, newValue)
1. Resets the identity value to newValue.
2. If 50, new record will have 51
3. You can se to negative value also
4. You can set to already inserted value also. If PK exists on that column, then while inserting it will throw error, not while setting the value
DBCC OPENTRAN
Gives open transaction information
DBCC SHOW_STATISTICS ('Domain.domainvalue', 'pk_Domainvalue')
Give the complete statistics of an index
DBCC SHOWCONTIG ('Domain.Domainvalue') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
System level infromation like # of pages, Avg free space per page, Logical Fragmentation in %

No comments:

Post a Comment