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
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.
Day 15 of 31 Days of Disaster Recovery: Running DBCC CheckTable in Parallel Jobs | SQLSoldier
[…] Fixing a Corrupt Tempdb […]
Day 16 of 31 Days of Disaster Recovery: Disaster Recovery Gems From Around The Net | SQLSoldier
[…] Fixing a Corrupt Tempdb […]
Day 17 of 31 Days of Disaster Recovery: When is a Checksum Written to a Page | SQLSoldier
[…] Fixing a Corrupt Tempdb […]
Day 17 of 31 Days of Disaster Recovery: When are Checksums Written to a Page | SQLSoldier
[…] Fixing a Corrupt Tempdb […]
Day 31 of 31 Days of Disaster Recovery: Backup and Restore of the Resource Database | SQLSoldier
[…] Fixing a Corrupt Tempdb […]