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