Are you Unable to Start SQL server services due to Suspect tempdb database?

Tempdb is a system database and has two files, one is tempdb log file (tempdb.ldf) & another is tempdb data file (tempdb.mdf). It is available for the all the database administrators that are connected to the instances of the SQL server. Tempdb is re-created every-time when you start the SQL server service. All the temporary tables and stored procedure of the tempdb are dropped automatically when a database administrator shutdown the SQL server that means there is no any record available in the tempdb database after SQL server shutdown. Database administrators use tempdb for storing temporary user objects, internal objects & row versions that are generated by data modification transaction. Here is the list of operations that can't perform on the tempdb system database:

• Can't perform Backup & Restore Operation on tempdb database
• Can't change the database ownership
• Can't run DBCC CHECKALLOC & CHECKCATALOC command on the database
• Can't set tempdb database offline
• Can't perform database mirroring & snapshot on the database

Sometime when you try to start SQL server services, you may unable to start because of tempdb database is marked as suspect and may get below error message:

“Database 'tempdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.”

Reasons: There are several reasons for above error message like 'file is missing', 'tempdb database structure corruption' and many more.

Steps to Fix: You can easily fix above error message by creating a new tempdb for the database. For creating a new tempdb database, follow given below steps:

• If tempdb database files exist then rename those files for example newname.ldf & newname.mdf.
• Start the SQL server from the command prompt by typing or pasting 'sqlservr -c -f -T3608 -T4022 command.
•  Now connect with the server by using query analyzer.
• Reset the status of tempdb database by using 'exec master..sp_resetstatus Tempdb' stored procedure.
• Now you have to shutdown the server by pressing CTRL+C in the window command prompt.
• Restart the SQL server, now you have a new tempdb database & recover old tempdb database.

Applied for: All above steps are applied for SQL server 2005