KoprowskiT_HUG-MSSQL_AdHocMaintenancePlansForBeginners

Each of experienced administrators used (to some extent) what is called Maintenance Plans - Plans of Conservation. During this session, I'd like to discuss what can be useful for us to provide
…

Each of experienced administrators used (to some extent) what is called Maintenance Plans - Plans of Conservation. During this session, I'd like to discuss what can be useful for us to provide functionality when we use them and what to look out for.

15.
FUNCTIONALITY: VERIFY INTEGRITY OF DATABASE
YOU SHOULD REMEMBER
• using DBCC CHECKDB
• Daily
• Weekly
• Monthly
• DBCC CHECKDB (’database_name’) WITH NO_INFOMSGS
• Suppresses all informational messages.
• DBCC CHECKDB (’database_name’) WITH NO_INFOMSGS, ALL_ERRORMSGS
• Displays all reported errors per object. All error messages are displayed by default. Specifying or
omitting this option has no effect. Error messages are sorted by object ID, except for those
messages generated from tempdb database.
• In SQL Server Management Studio, the maximum number of error messages returned is 1000.
• DBCC CHECKDB (’database_name’) NOINDEX
• Specifies that intensive checks of nonclustered indexes for user tables should not be performed.
This decreases the overall execution time. NOINDEX does not affect system tables because
integrity checks are always performed on system table indexes
Budapest | November 27th, 2013
15