Help: SQL Server

As promised in my previous blog, I am going to demonstrate few failures of database startup and technique to find the cause and fix them. First trick which you should always remember is that whenever you see any “operating system error” with some number (2, 5, 32 etc.) try to find the text of that error number. The trick here is to use “Net HelpMSG” windows command to convert the number to text. Here are few quick samples.

C:\WINDOWS\system32>net helpmsg 2
The system cannot find the file specified.

C:\WINDOWS\system32>net helpmsg 5
Access is denied.

C:\WINDOWS\system32>net helpmsg 32
The process cannot access the file because it is being used by another process.

Now, we would first cause DLL generation failure during startup of database. As learned in earlier post, database startup (a.k.a. recovery) process would fail.

Here is what we would see in ERROLROG.

2013-12-30 03:25:44.280 spid51       Starting up database ‘SQLSeverHelp_IMO’.
2013-12-30 03:25:44.520 spid39s      restoreHkDatabase: DbId 8, Msg 41312, Level 16, State 4, Unable to call into the C compiler. GetLastError = 2.
2013-12-30 03:25:44.520 spid39s      [ERROR] Database ID: [8]. Failed to load XTP checkpoint. Error code: 0x82000008. (e:\sql12_main_t\sql\ntdbms\hekaton\sqlhost\sqlmin\hkhostdb.cpp : 2558 – ‘RecoverHkDatabase’)

Now, you would understand why I spoke about Net Helpmsg command. Error 2 = The system cannot find the file specified. Now, next task is to find out which path/file. To find any activity done by a process, one of the famous tool to use is Process Monitor (free tool by Mark Russinovich). I have run the tool and applied filter for Process Name = sqlservr.exe. Here is the filtered information from process monitor.

It means that SQLServr.exe is looking for CL.EXE under C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\Xtp\VC\bin folder and unable to find the path. Indeed that is the problem. To do this failure, I have renamed bin folder to bin_xxx as shown below.

Once I renamed the folder back to “bin”, I was able to bring the database online.

I have simulated another failure of DLL generation by making some changes on operating system level and restarted SQL Server.

Always check errorlog and here is what we would see.

2013-12-31 20:01:35.040 spid20s      SetNamedSecurityInfoW failed for directory C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xtp for reason 5.

2013-12-31 20:01:35.040 spid20s      Error: 41334, Severity: 16, State: 2.
2013-12-31 20:01:35.040 spid20s      The code generation directory ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xtp’ cannot be created or set up correctly.
2013-12-31 20:01:35.060 spid20s      Error: 41334, Severity: 16, State: 2.
2013-12-31 20:01:35.060 spid20s      The code generation directory ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xtp’ cannot be created or set up correctly.
2013-12-31 20:01:35.060 spid20s      Error: 41334, Severity: 16, State: 2.
2013-12-31 20:01:35.060 spid20s      The code generation directory ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xtp’ cannot be created or set up correctly.
2013-12-31 20:01:35.170 spid19s      Error: 41334, Severity: 16, State: 2.
2013-12-31 20:01:35.170 spid19s      The code generation directory ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xtp’ cannot be created or set up correctly.

Important piece of error message is reason 5. If you recall what you read above, net helpmsg 5 = Access Denied.

How would you troubleshoot? Again same tool, process monitor. If we filter for all access denied (right click on Access Denied text and say "Include"), it’s clear that Service account is trying to generate the DLL of table but unable to do so.

Checking properties of XTP folder reveals the same. To break the things, I given “deny” on that folder to SQL Service account.

Once I removed “deny” and gave “allow”, I was able to bring database online using below command

Alter database SQLSeverHelp_IMO set online
go

Another possible reason of database not able to recover would be out-of-memory errors. Since all data needs to be loaded into memory before in-memory tables can be accessed. In-memory engine needs to have all the memory available to hold the data at startup of the database. If the system is low on memory or max server memory is set to too low, we might encounter recovery failure. In such situation, you may want to stop other processes running on the server so that SQL is having enough RAM to load in-memory tables.

I hope that this blog has given you some hints about possible failures, troubleshooting tricks and their resolutions.