If you missed any of the earlier posts in my DR series, you can check them out here:
- 31 Days of disaster Recovery
- Does DBCC Automatically Use Existing Snapshot?
- Protection From Restoring a Backup of a Contained Database
- Determining Files to Restore Database
- Back That Thang Up
- Dealing With Corruption in a Nonclustered Index
- Dealing With Corruption in Allocation Pages
- Writing SLAs for Disaster Recover
- Resolutions for All DBAs
- Use All the Checksums
- Monitoring for Corruption Errors
- Converting LSN Formats
- Extreme Disaster Recovery Training
- Standard Backup Scripts
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.