Corruption in TempDB

I received a contact message from the “Contact us” form, asking about how to go about repairing corruption in TempDB.

The error message was something like this:

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:118576; actual 0:0). It occurred during a read of page (1:118576) in database ID 2 at offset 0xxxxxxx in file ‘xxxxx\tempdb.mdf’.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

  The fact that you know you have corruption in TempDB is good news, that shows that you are running CheckDB against TempDB and many people overlook this.

    The corrupt page in TempDB may cause some issues if it is not cleared up.

    Since the TempDB files are recreated if they don’t exist when SQL Server restarts, here is what I would recommend.

1. First start by confirming that this file is part of tempDB.  I have seen people do crazy things like name a data file for a real database tempdb.mdf. Once you have confirmed the corruption is in TempDB, then proceed with the following. Since the error message references database ID of 2 it is likely TempDB.
2. Stop the SQL Server service.
3. Rename the TempDB.MDF file to some else like BadDB.XXX (we will delete it later).
4. Start the SQL Server service, confirm that everything starts correctly. The TempDB files will be recreated on start up.
5. Run CheckDB against TempDB and confirm that there is no corruption.
6. After everything is confirmed working, delete the BadDB.XXX file.

You will want to investigate why the corruption occurred, and of course you will want to run CheckDB against all your other databases to be sure there is no corruption there.

If you are having corruption in TempDB, you should not have much trouble if you follow these steps.

If you have more serious SQL Server database corruption, you can contact Stedman Solutions, and we can help.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!