SQL Server – 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:

  1. 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.)
  2. 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 eed 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.

   

2_Resolving_Disk_Missing_Issue_for_TempDB_with_a_Twist

You can also check the location where the drive is pointing to – Verify by just issuing command SUBST

3_Resolving_Disk_Missing_Issue_for_TempDB_with_a_Twist

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.

Hope you find it helpful.

 

Regards

Sarabpreet Anand

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

   

8 Comments on “SQL Server – Resolving – Disk Missing Issue for TempDB with a Twist”

  1. Thank you, thank you, thank you! This is a great tip. I wish I had about a year ago. Thanks again.

  2. Is SUBST persistent across reboots? For example, if I do this and the server reboots, will the SUBST’d drive still be there?

    Is SUBST visible across user sessions? For example, if I’m logged in under DOMAIN\Marc and SQL Server is running under DOMAIN\SQL, will SQL Server be able to see the SUBST’d drive?

  3. Reboot will clear created drives by SUBST, but we can resolve this by keeping the command in autoexe.bat file

Leave a Reply

Your email address will not be published.