Some useful Commands for Check and Maintain MSSQL Database

— Shrink Database
DBCC SHRINKDATABASE (AdventureWorks2008R2, TRUNCATEONLY);

— Rebuild All Index on a table
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);

— Reorganize all indexes on the HumanResources.Employee table.
ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ;

— Check DB for database consistency
DBCC CHECKDB

— Reclaims space from dropped variable-length columns in tables or indexed views
DBCC CLEANTABLE

— Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.
DBCC UPDATEUSAGE (AdventureWorks2012)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.