Following on from my previous blog post I mentioned that I had to find a solution to the “unable to call into C compiler” message. This meant that the specific SQL Server database in question (that contained in-memory tables) went into recovery pending mode, meaning that recovery needed to run but something was preventing it from even starting.
Naturally (whether right or wrong) I thought that it could be corruption and I didn’t want to rule it out. I headed straight to the error log to see if it could guide me, all this did was confuse me. However after some research it led me to conclude that “Unable to call into the C compiler GetLastError = 2” for the specific error code shown below meant that a specific file(s) could not be found, I was thinking exactly what file(s)?
This is a snippet from the error log when the database went through recovery – Seems to even struggle during the analysis phase and the checkpoint failure was throwing me off slightly.
Being desperate I tried code to switch it online.
ALTER DATABASE [NEWdb] SET ONLINE;
Intimidating right? I didn’t really know where to start but I headed down the CHECKDB route, if anything to rule it out from my checklist. In case you are wondering, what I personally do for a troubleshooting exercise is that I write things down regarding areas that I want to explore/analyse and ideally remove from the list.
ALTER DATABASE [NEWdb] SET EMERGENCY;
SELECT name, state_desc FROM sys.databases
I noticed the creation of a xtp folder where it was followed by another folder called 8 being the DBID (was never there before)
I then issued the consistency check – It was taking its time, perhaps a deep dive?
A great sign!? Not really. No corruption meant I had to go down a different route.
So it wasn’t corruption?
Alas, I was back at square 1.
I decided to track real time what SQL Server was trying to do via a tool called process monitor. I created a filter to look for sqlservr.exe and went through recovery and watched what was happening real-time. I could see the data files were being read for all the other databases, then I got to the problematic database and the “path not found messages caught my attention”.
Zoomed in version to confirm file path location:
This wasn’t a case of corruption, more so a corrupt install of SQL Server. I with my limited knowledge felt that bringing out the install binaries for a repair was my best option here.
As soon the repair completed I marched my way to the Binn folder to see if the VC folder was created along with other files (i.e. cl.exe) – and it was.
Was this what I needed then?
YES IT WAS – Recovery was successful and I was able to login and query my in-memory based tables.
Even though the ending might have been an anti-climax I hope you enjoyed reading how I conquered this mini-challenge. If you ever encounter this sort of issue in real-life then my advice is –
Then start your investigations with a clear mind.