SQL Server – Instance not starting or crashing immediately. Event 5161. TempDB error

By
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 and once you restart the server “boom”, either it will not come up at all or in case of cluster SQL Service will come up then it will fail causing the cluster to failover to secondary node and there also it will fail again. No matter how many times you do it, you'll end up with same results. If you search the Windows event viewer logs you'll find an error like the one below:


Now, fortunately this type of change does require a down-time so you must be having one already, however as we have that in limited amount, the below mentioned steps will help you overcome this situation rather quickly.

The Resolution
If you haven't caught it yet you must revisit the T-SQL commands image above and you'll notice a very small miss but a very big mistake. We have provided the same physical "file-name" for the 5th additional data file as we did for the first one i.e. “tempdev01.ndf”, though it should have been “tempdev05.ndf” and this is what causing SQL DB Service to crash immediately as two (or more depending on how many you have missed) logical “tempdb” files are trying to activate same physical file which is not possible.

Now the only way to make this work is to correct the “TempDB” database file mappings and that again require access to SQL Instance which is not starting here. To make it all up and running this is what you've to do:

1. First Go to Control Panel -> Administrative Tools -> Open Failover Cluster Manager on the active cluster node.

2. Expand the Cluster and go to nodes folder and right click to stop cluster services on the passive node to avoid any other "failovers". See below image for hint.


3. Now open the command prompt with “Administrator” privileges and type below command:
NET START MSSQLSERVER /m /T3608
What this command does is that it will start SQL Services in “master-only” configuration. This mode will bypass creation\recovery of “TempDB” database.

4. Now you can access SQL Instance using “SQLCMD”. In case you use windows authentication you can use the below command:
SQLCMD –E –S<servername>
Where <servername> will be your cluster name.

5. Once you gain access to the server you can reissue your correct alter database commands to rectify the mappings.

6. Once the commands have been executed successfully, exit SQLCMD mode and use the below command to stop services and start your SQL Server again using “Failover Cluster Manager” and you're good to go.
NET STOP MSSQLSERVER

7. Enable “Cluster Service” on the passive node to re-enable Active Cluster Configuration.

For stand-alone SQL instance number of steps are less for obvious reasons. 

1. Open command prompt in “Administrator” privileges and start SQL Server using following command:
Sqlservr.exe –T3608
“SQLSERVR.EXE” is generally available on the following location:
“C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn”

2. Follow step 4 and 5 from above to correct the “TempDB” file mappings.

This is just one of the case where due to misconfiguration of “TempDB” (incorrect physical file mapping or missing drives etc.) can cause SQL Server to crash. You can also go to the below link which is written by “Gail Shaw” a.k.a. “GilaMonster”, where she has beautifully discussed many such cases in details and show us the way out of such messy situations.



MCSA Certified SQL Server DBA with sublime development skills to automate regular tasks and interest in following and discussing trending technologies, well-being and spirituality.

Comments

Post a Comment

Popular posts from this blog

SQL Server: SQL Server services not starting. TDSSNIClient initialization failed with error 0x139f, status code 0x80. Reason: Unable to initialize SSL support.

By
Hi All, and welcome back. As the computing power and networking is increasing day by day so is the need of more safer and stronger encryption channel methods and algorithms. And we want our databases and database server to use the same as well. But sometimes implementing them may cause undesired results like SQL Server not coming online. In case you’re also facing the same issue or planning to adopt new TLS standards like TLS 1.2 and don’t want to such problems then you should go through this post. Problem: We may require to make changes like enabling higher level cipher suite TLS 1.2 and disable TLS 1.0 for better security of our servers. But in case you make this changes it can be possible that after restart the SQL Services refuse to come online and fails. If you check the SQL Server error logs you’ll get the below errors: Error: 17182, Severity: 16, State: 1. TDSSNIClient initialization failed with error 0x139f, status code 0x80. Reason: Unable to initialize SSL sup
Read more

SQL Server 2014 SP\CU installation getting stuck at “MsiTimingAction”

By
Image
Hello folks, and welcome back to this new post on Sidd's SQL Solutions. Recently I was working on regular SQL Server patching stuff, which often we have to do and I faced this odd situation on one of the server, which troubled me a lot. Fortunately I was able to fix it after two days of struggle, which also gave me my new post. This blog post will cover this issue and its fix in details. The Issue: The odd situation or the issue I faced here was while installing S ervice P ack 2 for SQL Server 2014 on a server running with  Windows Server 2012 R2  OS. The setup starts up normally like others but gets stuck at “MsiTimingAction ” page and  simply refuses to progress any further . No matter how many times you restart the setup or your system\server it end up at the same stage. It seems to be a known issue, which was fixed in C umulative U pdate 2 of S ervice P ack 1 for SQL Server 2014. The system on which I was applying this patch was already running with CU8
Read more

SQL Server: Cluster Installation failed with error “Wait on the Database Engine recovery handle failed.”

By
Image
So, it is a nice fine day and you’re in office doing your BAU. You got this new task of installing SQL Cluster (which you’ve probably done several times before) and after making all necessary arrangements and planning you started it. All went fine and then when the setup is almost done you got this strange error “Wait on the Database Engine recovery handle failed.” which ruins the rest of your day. No worries we are here to sort this out quickly for you. Problem Now the problem here is that SQL Server setup is able to install all the components but is failing for DB engine (and its group). You get the error pop-up stating the below error: “Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.” It may also give some link, which unfortunately (may) not lead you to anywhere. And at the end your see crosses against DB engine and its dependent components. Let me first tell you that why you got this error. In every SQL
Read more