Are these additional checks necessary/important? (Indexed views are probably a bit more concerning to me, I don't think we are using Service Broker or FILESTREAM yet.)
You can run DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS directly on the indexed views. Checking indexed views can be problematic in certain circumstances, so be prepared to investigate any false positives that result. (Paul Randal also mentions in the comments to the referenced article that false negatives are also possible, but I have no direct experience of that.)
If so, are there ways to perform these additional checks separately?
There's no support for running the Service Broker or FILESTREAM checks separately, no.
CHECKALLOC and CHECKCATALOG seem to run very quickly, even on large dbs. Any reason not to run these every day?
Not that I am aware of.
You might also consider running
DBCC CHECKCONSTRAINTS. This check is
not included in
DBCC CHECKDB, regardless of any options you may specify. You may also want to think about
occasionally running
CHECKDB, as and when circumstances permit.
I think @MartinSmith nailed it in his comment. When you have AUTO_CLOSE set to ON, then the database will shutdown when the last user exits. Likewise, when somebody attempts to use the database it will reopen. When the database reopens, a message about the last time DBCC CHECKDB ran will be logged.
Take the below example (sample code, not meant to be run anywhere near a production environment) to show proof of this:
create database AutoCloseDb;
go
alter database AutoCloseDb
set auto_close on;
go
dbcc checkdb('AutoCloseDb');
go
-- rotate the below "use" statements a few times in order
-- to cause the database to close and reopen
--
use AutoCloseDb;
go
use master;
go
After we do a few rotations of use AutoCloseDb; followed by use master;, we would see something similar to the below in the SQL Server error log (I use the below PowerShell code to retrieve this):
Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("<your SQL Server name>")
$SqlServer.ReadErrorLog() |
Where-Object {$_.Text -like "*AutoCloseDb*"} |
Select-Object LogDate, Text |
Format-List
And in my test environment, the output is the following:
LogDate : 11/15/2013 5:47:17 AM Text : Starting up database
'AutoCloseDb'.
LogDate : 11/15/2013 5:47:18 AM Text : CHECKDB for database
'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
(local time). This is an informational message only; no user action is
required.
LogDate : 11/15/2013 5:47:18 AM Text : Starting up database
'AutoCloseDb'.
LogDate : 11/15/2013 5:47:18 AM Text : CHECKDB for database
'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
(local time). This is an informational message only; no user action is
required.
LogDate : 11/15/2013 5:47:19 AM Text : Starting up database
'AutoCloseDb'.
LogDate : 11/15/2013 5:47:19 AM Text : CHECKDB for database
'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
(local time). This is an informational message only; no user action is
required.
LogDate : 11/15/2013 5:48:25 AM Text : Starting up database
'AutoCloseDb'.
LogDate : 11/15/2013 5:48:26 AM Text : CHECKDB for database
'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
(local time). This is an informational message only; no user action is
required.
LogDate : 11/15/2013 5:49:30 AM Text : Starting up database
'AutoCloseDb'.
LogDate : 11/15/2013 5:49:30 AM Text : CHECKDB for database
'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
(local time). This is an informational message only; no user action is
required.
If it is indeed AUTO_CLOSE that is causing this logging behavior in your case, you should see the similar message of "Starting up database ..." in your error log.
If this is not what you're seeing, then please modify your question to include other error log events surrounding the DBCC CHECKDB logging.
Best Answer
The only alternative to performing consistency checking (
DBCC CHECKDB) is to leave your data open to the possibility of corruption and data loss.There are some ways to reduce the impact that
CHECKDBhas on your system. The best way to do this by far is to run your checks offline on a copy of your live systems. This should be done by restoring a full backup of your database onto another system and then runningDBCC CHECKDBthere. This is good for two reasons:It off loads the IO and perfomance issues your are experiencing onto another machine
It tests your backups restore successfully.
If you can't afford to have a secondary server for testing backups and running
DBCC CHECKDBthen you may have to break the check down and run it against a few individual tables each night to spread the load out accross the week. You can do this with theCHECKTABLEstatement. Another way to break the job into smaller parts would be to useCHECKFILEGROUPto check each file group, only makes sense if you have multiple file groups.You can also try
trace flags 2549, 2562, and 2566. For more information on the trace flags, the methods mentioned above and more read Aaron Bertrand's post on the subject here