How to Troubleshoot if hard drive crash where TEMPDB installed in SQL Server?

Someday before we found one strange issue on one server it was a test server so it was not much critical.

The issue was someone from windows team has removed one drive from the server and in that drive we have installed Tempdb during SQL server installation .So our SQL Server start working on this server because without TempDB our SQL Server never start . So we try to found the issue and we start investigation on this why SQL Server not starting on this Server. Finally we got some errors in windows event log  like

Following error message found in Event viewer :-

“FCB::Open failed: Could not open file G:\TEMPDB\tempdb.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).”

“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.”

The above error states it’s related to TEMPD DB issue and its clearly point that tempDb was in G:\ Drive and when we go to my computer we found that G:\ drive was missing from the server .So, we got to know this is the issue due to SQL Server not starting. We contacted to windows team on this who removed the drive and that person was not available that time and we need to start this server as soon as possible because it was urgent team by another team to test something in urgent basis.

So what next we think about this and got one idea to resolve this issue see below the step for more info:-

   

Step 1:-

We go to configuration manager and tried to start MSSQL Services but no luck:-

Step 2:-

Then we start SQL Server in Single user with Master trace “/m /T3608”and its started.

With following commands:-

Run CMD with Administrator rights

C:\Windows\system32>NET START MSSQLSERVER /m /t3608

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service was started successfully.

Step 3:-

Here we are modifying the path of TempDb that is showing above in error log:-

 “G:\TEMPDB\tempdb.mdf” to “E:\TEMPDB\tempdb.mdf”(new path)

C:\Windows\system32>SQLCMD

1> Alter database tempdb modify file (name = tempdev, filename = 'E:\TEMPDB\tempdb.mdf')

2> GO

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.

1> Alter database tempdb modify file (name = templog, filename = 'E:\TEMPDB\templog.ldf')

2> GO

The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Stop SQL Server and restart SQL Server normally and its start normally and it changed the path of TEMPDB now G:\ to E:\ drive :-

C:\Windows\system32>NET STOP MSSQLSERVER

The SQL Server (MSSQLSERVER) service is stopping.

The SQL Server (MSSQLSERVER) service was stopped successfully.

Step:- 4

You can see the services now up and running

Step 5:-

You can run below command and can Check the exact location of your Tempdb :-

So Its Done ! J  Thanks !