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. Restart SQL Server. The files will be reinitialized and corruption should be gone. Many people have come to believe that if you restart SQL Server, the tempdb files will be recreated anew. 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

On some rare occasions, simply restarting SQL Server did not resolve the tempdb corruption. It’s been a long time since I encountered this scenario, and my attempts to reproduce the issue were fruitless. You are not likely to encounter an issue where simply restarting SQL Server doesn’t fix the corruption. If you encounter this issue, delete the corrupted file or files 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.

Thanks to Paul Randal (blog|@PaulRandal) for pointing out that deleting the tempdb files are usually not needed.