Is it recommended to run DBCC CheckDB on tempdb? If yes, can someone please list out the reasons to do so? This could be on any SQL Server version. The one I have is SQL Server 2017.
Sql-server – DBCC checkdb on tempdb
dbcc-checkdbsql server
Related Question
- Sql-server – DBCC CHECKDB LastKnownGood
- Sql-server – DBCC CHECKDB errors
- T-sql – Date of Last DBCC checkdb run
- SQL Server DBCC CHECKDB Error – How to Resolve
- Sql-server – DBCC CHECKDB grows tempdb to a huge size – Legacy MS SQL Server 2008 R2 Instance
- SQL Server – How to Find Duration of Last DBCC CHECKDB
- DBCC CHECKDB – Differences Between PHYSICAL_ONLY, FULL, and DATA_PURITY
Best Answer
No, it's not recommended because it's largely a waste of time. When SQL Server restarts, tempdb is created from scratch. Also, the data in there isn't around long enough to be a good indicator of if there's an issue with it, or the underlying storage.
Both Ola's scripts and Maintenance Plans will skip tempdb when you set up CHECKDB routines.
Another reason why it's largely useless is because if tempdb does get corrupted, you'll know in a hurry. Check out my blog post here where I corrupt tempdb on purpose: DBA Days: Killing TempDB softly
Coming up in SQL Server 2019, we'll be able to use "in memory" technology for system tables in tempdb. While this solves pretty big contention issues, you can't run CHECKDB against in-memory tables. The only way you could check those is if you backed up tempdb, which isn't something you can do.
If you really think it's worth running checkdb against a database that SQL Server won't allow you to back up, well, it's your maintenance window, I suppose.