Sidd's SQL Solution

This is one of the (nightmare) situations in which no DBA want to get into, especially if it is your PROD SQL Server. But if you've already got yourself into one of such situations then you've come to right place. In this article we will see how to overcome one such situation where SQL Server instance doesn't come back or crashes immediately after starting the services in case you've just changed the location of your “TempDB” database. Test Scenario To start with we picked a test SQL Server Cluster and added few more “Tempdb” files to it in its default location which was in C: drive here.  Why Cluster? Because clusters are bit more complicated to troubleshoot and also took a while to get to it, so, I thought it is good if I go this way. We will however also cover steps for stand-alone server as well.  Once all the files have been added we used the following T-SQL statements to move them to new location. All the commands executed successfully