sp_Blitz Last good DBCC CHECKDB over 2 weeks old - tempdb

  • webrunner

    One Orange Chip

    Points: 29627

    Hello experts,

    I ran Brent Ozar's script sp_Blitz, and one item it returned was this:

    -------

    Last good DBCC CHECKDB over 2 weeks old tempdb

    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 says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Ola Hallengren

    Hall of Fame

    Points: 3960

    This is by design. DatabaseIntegrityCheck is not doing checkdb on tempdb.

    Ola Hallengren

    http://ola.hallengren.com

  • webrunner

    One Orange Chip

    Points: 29627

    Thank you, Ola,

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • nick.broughton

    SSC Journeyman

    Points: 91

    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?

  • Ola Hallengren

    Hall of Fame

    Points: 3960

    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

    https://ola.hallengren.com

  • Jeff Moden

    SSC Guru

    Points: 993661

    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

    https://ola.hallengren.com

    Rather than taking it offline, can you keep it here so that others might benefit, as well?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • nick.broughton

    SSC Journeyman

    Points: 91

    Thanks Ola

    My version is fairly recent and that works. I'm actually using 'ALL_DATABASES,tempdb' now.

  • Ola Hallengren

    Hall of Fame

    Points: 3960

    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.

  • Jeff Moden

    SSC Guru

    Points: 993661

    Now that's some good info. Thanks, Ola.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • spaghettidba

    SSC Guru

    Points: 105661

    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.

  • Thomas Mucha

    SSC Eights!

    Points: 822

    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 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply