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.
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.
BACKUP DATABASE tempdb TO DISK = 'D:\Backups\t.bak'
Msg 3147, Level 16, State 3, Line 27 Backup and restore operations are not allowed on database tempdb. Msg 3013, Level 16, State 1, Line 27 BACKUP DATABASE is terminating abnormally.
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.
Complementing to Erik's answer
Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.
The only reason that I can think of running a checkdb against tempdb is when tempdb gets badly corrupted that the sessions using it starts to get errors.
Also, when tempdb gets corrupted, it is possible that your user databases might have corruption as well.
I personally do not have checkdb done on tempdb.
I have a different perspective here, you "should" run checkdb against all the databases no matter system or user period.Why, simply because it is database which stores information what if there is corruption are you going to restart the SQl Server so that tempdb is cleared and "may be" corruption is gone. I would also like you to read Brent Ozar blog
Q: Should I run CheckDB against TempDB?
Yes– you should. CheckDB can’t do every single check against TempDB that it can against other databases (the output will let you know that it can’t create a snapshot), but it can still run a variety of checks against TempDB and it’s supported to run it. Don’t skip it! You should be checking master, model, msdb and tempdb, plus all your user databases.
Should I run checkdb on Tempdb as often i do for user database ? No you can avoid that if required.
Should I not run checkdb at all on tempdb database ? No, you should run. Do not omit it.