Resolving – Disk Missing Issue for TempDB with a Twist
A not so common but major issue could be missing Disk\Drive issue for any SQL Server, If there were user DB files on that disk chances are you’ll lose some data if the same Disk is not allocated again to you, Moreover you have to bear downtime. (Though there are some exceptions to it which we’ll discuss in another blog post.)
But in case your TempDB was there on missing Drive\location – it will not even allow your SQL Server to start & gives the below error:
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘S:\TempDB_Date\tempdb.mdf’.
The only two options left with you are:
- Ask your storage admin to allocate a LUN (fresh\old one) – Map the storage to Server, assign the same drive letter, create the folder if it’s not there already and then start your SQL Server. (You might also need to work on permissions if there is a need.)
- If you have the space available on any other drive, start your SQL Server in Single User Mode, make changes to location of TempDB files with an Alter command and then restart SQL Server in normal mode.
Both the above solutions will work but the problem is you might not have enough time to implement either of these because if you ever encounter such issue on Tier 1 or Tier 2 Server a Sev.1 will be there immediately awaiting your response and on top of that you need to represent your tower on a Bridge call to answer multiple questions (what\where\when\why etc.) all of this add stress and between all this you need to take multiple steps to solve this issue.
Your SLA will also hit if you are not able to resolve the issue in given time. (SLA might depend but starting SQL in Single User mode or getting approval & allocating storage on server needs time)
Quickest Solution
The quickest solution could be learning just one dos command. Yes the very old Dos expose a highly powerful command which can resolve the issue under few minutes if not in seconds.
In my example the S: Drive was missing and here is the screenshot of the available drives.
Now, All you need to do is find the drive\disk where you have enough space on the server, and issue the below command:
SUBST S: D:\Sarabpreet\SSG_Example
–You need to replace the First parameter with Drive Letter you need and Second Parameter with the location\Drive where you have enough space to create TempDB Files
By issuing the above command it will map the location (Path) to Drive letter given as a second parameter.
You can also check the location where the drive is pointing to – Verify by just issuing command SUBST
Post that you can create the folder and restart the SQL Services.
If you want to delete the created drive once the disk has been allocated back to you, you can use the /D switch.
Note: Reboot will clear created drives by SUBST, but we can resolve this by keeping the command in autoexe.bat file
Hope you find it helpful.
Regards
Sarabpreet Anand