Do we need to perform DBCC CHECKDB for TempDB

  • pshaship

    SSCertifiable

    Points: 6125

    Hi,

    I'm performing DBCC CHECKDB for all databases. But I'm not sure that we can perform DBCC CHECKDB on TempDb too.. Is that allowed?

    Because I'm using the maintenance plan Integrity check and this job NOT performing DBCC CHECKDB for tempDb.

    Please advice

    Thanks

  • Welsh Corgi

    SSC Guru

    Points: 116520

    It will not run properly because you can't have a snapshot of tempdb.

    It is not necessary for it is temporary and it gets recreated each time you restart the server.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • naveengoud.dba

    Valued Member

    Points: 73

    we cannot perform DBCC CHECKDB on TempDB

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Please note: 4 year old thread.

    And you can run CheckDB on TempDB. It doesn't do the same amount of checks as for other databases, but it can be run.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • webrunner

    One Orange Chip

    Points: 29866

    GilaMonster (6/18/2015)


    Please note: 4 year old thread.

    And you can run CheckDB on TempDB. It doesn't do the same amount of checks as for other databases, but it can be run.

    Thanks, Gail. I know this thread is really old, but I have a question about it.

    I ran this command that I found on dba.stackexchange

    http://dba.stackexchange.com/questions/80189/tempdb-not-selectable-for-dbcc-maintenance-plan

    DBCC CHECKDB(N'tempdb') WITH NO_INFOMSGS

    I tested it on my dev and staging SQL servers and it ran really fast, about 1 second. Did the command actually do any checking of tempdb? Or did it just execute successfully but not do anything useful?

    The reason I ask is that I used Brent Ozar's sp_Blitz tool and it returned a row saying "Last good DBCC CHECKDB over 2 weeks old" and "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."

    Thanks for any help.

    - 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

  • Jack 95169

    SSC-Addicted

    Points: 407

    I'm getting a SQL Server Alert System: 'Severity 021' message as of about 90 minutes ago

    Unable to find index entry in index ID 0, of table 340842130, in database 'tempdb'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE

    Select object_name(340842130) gives me a tablename of NULL

    dbcc checkdb (tempdb) with no_infomsgs runs and reports no issues

    A few minutes later I get the same alert, albeit with a different table number

    There seem to be no other sysmptoms - I'm thinking of leaving it to the maintenance window and then restarting the service so that TempDB is recreated

Viewing 6 posts - 1 through 6 (of 6 total)

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