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
- Fixing a Corrupt Tempdb
- Running DBCC CheckTable in Parallel Jobs
- Disaster Recovery Gems From Around The Net
- When are Checksums Written to a Page
- How to CHECKDB Like a Boss
- How Much Log Can a Backup Log
- The Case of the Backups That Wouldn’t Restore
- Who Deleted That Data?
- Which DBCC CHECK Commands Update Last Known Good DBCC
- Restoring Differential Backups With New Files
- Handling Corruption in a Clustered Index
- Improving Performance of Backups and Restores
- The Mysterious Case of the Long Backup
- Restoring Part of a Database
So let’s say that you’re running queries on your SQL Server one day and suddenly the query fails saying there was a transport error. You run it again, and the query window is not able to connect to the server again. You check the SQL services and you see that SQL isn’t running. Obviously, you try to start SQL running again via SQL Server Configuration Manager, and it shows that SQL Server started. You try your query again and get the same result. Next, you find the SQL error log and open it in Notepad, and you see the following errors:
Clearing tempdb database. Error: 5123, Severity: 16, State: 1. CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'E:\tempdb\tempdb.mdf'. Error: 17204, Severity: 16, State: 1. FCB::Open failed: Could not open file E:\tempdb\tempdb.mdf for file number 1. OS error: 3(The system cannot find the path specified.). Error: 5120, Severity: 16, State: 101. Unable to open the physical file "E:\tempdb\tempdb.mdf". Operating system error 3: "3(The system cannot find the path specified.)". Error: 1802, Severity: 16, State: 4. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
SQL can’t create or open the tempdb files. The errors state that it can’t find the path specified. So you look at disk manager, and the drive is gone. It’s dead. Unrecoverable. Well, we’re not worried about losing anything in tempdb, we just want to get the server up and running again. One option is to bring another drive online with the same name as the old one and let tempdb recreate its files at startup. Let’s assume that’s not an option for you at this time and you’re only recourse is to change where tempdb is looking for it’s files. How do you do that if you can’t start it to change the file locations of SQL Server?
The solution is to start SQL Server in minimal configuration with trace flag 3608. Trace flag 3608 skips startup of all databases except master. Then connect via SQLCMD and make the changes to the tempdb file paths. Open a command window and run the following command to start SQL Server. In this example, I am starting a named instance named SQL13 so the server name format is MSSQL%
net start mssql$sql13 /f /t3608
Then I connect via SQLCMD and modify the file paths. Since I start SQL in minimal configuration mode, I will need to connect via the Dedicated Admin Connection (DAC) (-A option). Just make sure you created the required directory structure for the tempdb files. The two commands I will run (1 for each tempdb file) are:
sqlcmd -A -S SQLLaptop\SQL13 -q"Alter Database tempdb Modify File (Name = 'tempdev', FileName = 'G:\tempdb\tempdb.mdf');" sqlcmd -A -S SQLLaptop\SQL13 -q"Alter Database tempdb Modify File (Name = 'templog', FileName = 'G:\tempdb\templog.ldf');"
Then just restart the service and you’re good to go.