Day 14 of 31 Days of Disaster Recovery: Fixing a Corrupt Tempdb

Welcome to day 14 of my 31 Days of Disaster Recovery series. I’ve previously discussed handling corruption for nonclustered indexes and allocation pages. Today, I’m going to talk about a specific corruption that is very simple to fix; however, it does mean taking the server offline briefly. How brief depends on a few things, such as is the corruption in the log file or the data file. And if it’s in the data file, is Instant File Initialization (IFI) enabled. If it’s not, you should enable IFI now unless you have compliancy restrictions against it. Go do it now, this blog post can wait. I am, of course, talking about corruption in the tempdb database.

If you missed any of the earlier posts in my DR series, you can check them out here:

31 Days of disaster Recovery
1. Does DBCC Automatically Use Existing Snapshot?
2. Protection From Restoring a Backup of a Contained Database
3. Determining Files to Restore Database
4. Back That Thang Up
5. Dealing With Corruption in a Nonclustered Index
6. Dealing With Corruption in Allocation Pages
7. Writing SLAs for Disaster Recover
8. Resolutions for All DBAs
9. Use All the Checksums
10. Monitoring for Corruption Errors
11. Converting LSN Formats
12. Extreme Disaster Recovery Training
13. Standard Backup Scripts

Tempdb Corruption

In the early days of SQL Server 2008 RTM, I saw a lot of tempdb corruption when people started implementing transparent Data Encryption (TDE). There was a SQL Server bug in RTM that could cause this. Yes, folks, I am admitting that corruption is sometimes caused by SQL Server … like .01% of the time. The fix for tempdb corruption is simple. Shut down SQL Server, delete the tempdb files, and start SQL Server again. What I find instead is that many people have come to believe that all they need to do is restart SQL Server and the tempdb files will be recreated new. This is the fault of Books Online. Books Online states that the tempdb files are recreated each time the SQL service starts. This is a myth that was proven false by Jonathan Kehayias (blog|@SQ:Poolboy) here: does-the-tempdb-log-file-get-zero-initialized-at-startup.aspx

So if you don’t delete the existing tempdb files, they will be reused and the corruption will likely still be there. The key is to delete the corrupted file while SQL Server is shut down. As I mentioned before, if it is the log file or if IFI is not enabled, then the restart time will likely be longer than usual. Log files must always be zero initialized when created. Just be aware of how big the file is configured to be and be prepared for the startup to be delayed.

Fixing corruption doesn’t get easier than this, but you shouldn’t stop there. Fixing corruption is only part of the job. You need to investigate the root cause of the corruption. If it’s a SQL bug (probably not), then you need to make sure you have applied the fix or contact PSS to get one. You also need to investigate the disk subsystem to see if it is external corruption. Whatever the cause of corruption, you need to address it to prevent it from occurring again. A DBA’s primary job is to protect the data, that means prevention is just as important as fixing what’s broken.