Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

A-Z of In-Memory OLTP : Troubleshooting database Startup and recovery failures

Posted by blakhani on January 2, 2014


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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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

image

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.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • 11 Responses to “A-Z of In-Memory OLTP : Troubleshooting database Startup and recovery failures”

    1. […] for OS error 53. Sometime this could happen with any other OS error as well. Refer my earlier post here where I showed how to get text for an operating system error number. 53 = The network path was not […]

    2. Wenham Taxi said

      Way cool! Some exttremely valid points! I appreciate you penning this
      post and the rest of the site iss extremely good.

    3. manu said

      Completely agree with “Wenham Taxi” comments above.

    4. Ana said

      Thanks for sharing the knowledge Balmukund.
      How about restoring an In-memory database from one server to another? In an attempt to try the same, I am getting error for Inmemory filegroup,
      SQL Server Assertion: File: , line=1689 Failed Assertion = ‘hkLog->log_varData.ElementCount () == 1’. This error may be timing-related. If the error persists after rerunning the s
      A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB.

      Please share your thoughts here and let me know what wrong I am doing.,

      • blakhani said

        Could you please let me know “Select @@version” on Source and Destination.

        • Ana said

          Destination: Microsoft SQL Server 2014 (CTP2) – 12.0.1740.0 (x64) Enterprise Evaluation Edition (64 bit) on Windows NT 6.2
          Source: Microsoft SQL Server 2014 (CTP2) – 12.0.2342.0 (x64) Enterprise Edition (64 bit) on Windows NT 6.3

        • blakhani said

          that’s CTP (pre-release) version. can you please download Eval RTM version and try.

    5. Balu sir,
      I have 2 questions:
      1) If in case the available memory is less than the size of the in memory table, is there no way to being the database online(if in case someone is not able to salvage enough memory which SQL Server can grab.

      2) On large enterprise where the data in OLTP tables is huge (Lets say one of my biggest table is 3TB), how would this in memory table solution would fit in? Is there a way we can put partial table data in memory or if there is not memory the data will be automatically sent to the disk. Something like that. As an example:
      If I have allocated 1TB RAM to SQL Server and know that during the course of time the in memory table will grab around 500 GB of memory. Upto no every thing is OK. But lets say no one noticed and the table grew to 1TB, then there will be issues. In this case, is there no solution but to add more RAM?

      Regards
      Abhay

      • blakhani said

        1. Your inserts itself would fail if memory is not sufficient. If database size is more than max server memory (if you have changed) then recovery would fail. Nothing can be done other than adding more memory.

        2. Correct. Currently there is no “partition” feature to keep partial data on “disk-only”. Please provide feedback to product group by filing connect item.

    6. Done 🙂

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: