August 21, 2014 at 2:47 pm
Hello experts,
I ran Brent Ozar's script sp_Blitz, and one item it returned was this:
-------
Last good DBCC CHECKDB over 2 weeks oldtempdb
Database [tempdb] never had a successful DBCC CHECKDB. This check should be run regularly to catch any database corruption as soon as possible. Note: you can restore a backup of a busy production database to a test server and run DBCC CHECKDB against that to minimize impact. If you do that, you can ignore this warning.
-------
For this SQL Server (a staging server) I use Ola Hallengren's SQL Server Maintenance Solution, with this setup for checking database integrity:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d utility -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @databases = 'SYSTEM_DATABASES', @LogToTable = 'Y'" -b
I now realize I may need to give a fuller set of parameters to the sp, but even so, I have set the @databases value to 'SYSTEM_DATABASES'. When I check the corresponding function in the tool that is supposed to retrieve the databases to check, I see this:
IF @CurrentDatabaseName = 'SYSTEM_DATABASES'
BEGIN
INSERT INTO @Database03 (DatabaseName, DatabaseStatus)
SELECT [name], @CurrentDatabaseStatus
FROM sys.databases
WHERE [name] IN('master','model','msdb','tempdb')
END
I see tempdb listed there, but not in the log file for the job when it's run. In the log file, master, model, and msdb are being checked. But not tempdb.
Does anyone know if this is (1) a bug in Ola's scripts, or (2) just that CHECKDB doesn't run against tempdb? Or (3) just that I misconfigured it?
Ola's scripts are really excellent, so I think the issue is (2) or (3), but at this point I can't be sure.
Thanks in advance for any help. I'm hoping someone here knows the answer, but if I should just contact Ola, let me know.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 22, 2014 at 2:29 am
This is by design. DatabaseIntegrityCheck is not doing checkdb on tempdb.
Ola Hallengren
August 22, 2014 at 7:23 am
Thank you, Ola,
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
February 23, 2015 at 7:58 am
I've just come up against exactly the same issue.
Is there a difference of opinion between Brent and Ola on the necessity of checking tempdb?
I run a series of maintenance tasks on a Saturday morning. There's no user impact at that time, so I'm not concerned about that.
I can see how I would modify the SP to include TempDB, but I don't really want to do that. Why is it excluded? Is it a possibility to have a parameter to over-ride this in a future version?
February 23, 2015 at 9:19 am
In the latest version you can do like this:
EXECUTE dbo.DatabaseIntegrityCheck @databases = 'SYSTEM_DATABASES,tempdb', @CheckCommands = 'CHECKDB'
Please send me a mail for more details.
Ola Hallengren
February 23, 2015 at 9:42 am
Ola Hallengren (2/23/2015)
In the latest version you can do like this:EXECUTE dbo.DatabaseIntegrityCheck @databases = 'SYSTEM_DATABASES,tempdb', @CheckCommands = 'CHECKDB'
Please send me a mail for more details.
Ola Hallengren
Rather than taking it offline, can you keep it here so that others might benefit, as well?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2015 at 9:48 am
Thanks Ola
My version is fairly recent and that works. I'm actually using 'ALL_DATABASES,tempdb' now.
February 23, 2015 at 7:41 pm
When I developed the scripts I was looking at how Microsoft was doing in the Maintenance Plans. They are not doing checkdb for tempdb, so I did also not do that. Some time ago I added support for checking of tempdb, but not as the default. You need to specify tempdb explicitly.
February 23, 2015 at 7:45 pm
Now that's some good info. Thanks, Ola.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2015 at 3:16 am
I don't like checking tempdb for corruption for two reasons:
1) It can't run on a snapshot, so it takes locks instead. If you have a 24/7 workload, it's out of question.
2) If tempdb gets corrupted, it's not guaranteed to affect the instance. It might also get corrupted on objects that are not affecting your workload. In the worst case, you'll get errors and corruption alerts, so you'll have to stop the instance, delete tempdb files and start the instance again. Will checking tempdb on schedule help with that? No. So, what's the point of checking it on schedule?
YMMV, this is just my two cents. I'm genuinely interested in knowing if I'm overlooking something.
-- Gianluca Sartori
August 17, 2015 at 4:54 am
Based on Brent Ozar's sp_Blitz I've become convinced that running CheckDB on TempDB is a good idea. You can read his posts on the subject. The problem I ran into was that TempDB is often locked causing a DBCC CheckDB job to fail. I go around this by writing a little script to check for exclusive locks before executing the command. it's worked pretty well for me (I'm sure there are scenarios where an exclusive lock will be taken after the DBCC command has started, but so far, my implementations have worked well). I don't know if Ola's solution deals with this issue or not as I haven't tested it on TempDB. And you could obviously put Ola's script inside my loop, rather than the straight ahead DBCC CheckDB command.
DECLARE @tab table (spid int, db int, ObjId int,IndId int,[Type] Varchar(10), Res Varchar(100), Mode Varchar(10), [Status] Varchar(20))
WHILE (1=1)
BEGIN
INSERT INTO @tab
exec sp_lock
if (select count(*)
from @tab
where db_name(db) = 'tempdb' and Mode = 'X') = 0
BEGIN
DBCC checkdb ('tempdb')
BREAK
END
DELETE FROM @tab
WAITFOR DELAY '00:00:10'
END;
In SQL there are no absolutes, it always depends...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply