I just had to figure out how to do this so I figured a quick blog post is in order to save other people time in future.

If you ever need to use windbg to debug a SQL Server crash dump, or you want to capture call stacks using extended events (e.g. when debugging excessive spinlock contention), you’ll need the correct symbol file (sqlservr.pdb) to go with sqlservr.exe of the instance you’re interested in.

KB article 311503 has details on how to do this in general, but they’re a little cryptic so I’ll explain here.

Download and install the Windows debugging tools (see here). Unless you’re using Windows 8 or 8.1, download the tools for Windows 7/XP. If you find the tools install fails, try the 8.1 tools instead. Some people have reported failures and I’m afraid I don’t know how to debug these. Google around and see if you can find an option that works for you.

Now you’ll have a tool called symchk in the folder where windbg resides (for my laptop, “C:\Program Files\Debugging Tools for Windows (x64)”) – this is what will pull down sqlservr.pdb.

You need to point symchk at the executable you’re interested in, tell it where to put the sqlservr.pdb, and tell it the location of the Microsoft symbol server.

For me, the following worked:

C:\Users\Paul>cd C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn

C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn>C:\Program Files\Debugging Tools for Windows (x64)\symchk sqlservr.exe /s SRV*c:\symbols*http://msdl.microsoft.com/download/symbols

Then go to the c:\symbols directory, and find the directory called sqlservr.pdb. It will have one or more sub-directories with GUID names, so pick the one with today’s date and then copy the sqlservr.pdb from that directory into the \Binn directory.

Again, the command string to use once you’re in the SQL Server Binn directory is:

C:\Program Files\Debugging Tools for Windows (x64)\symchk sqlservr.exe /s SRV*c:\symbols*http://msdl.microsoft.com/download/symbols

If you get an error “The filename, directory name, or volume label syntax is incorrect.”, the copy-paste inserted weird characters for the double-quotes so delete and reinsert them. Also make sure there’s a space between /s and SRV.

You’ll also need to do this for sqlos.dll, plus on  SQL Server 2012 you’ll need to do it for sqlmin.dllsqldk.dll, sqllang.dll, sqlboot.dll too otherwise call stacks won’t resolve properly. I usually just replace sqlservr.exe in the command above with *.dll and then I’ve got all the symbol files possible, but you can save time by only doing it for the strictly necessary files.

If you’re missing some of the symbol files, your call stack will look something like this:

When you get to the analysis phase, if you don’t have all the right symbols, your call stack will look something like this (an example where sqlmin.pdb and sqllang.pdb are missing:

XeSosPkg::wait_info::Publish+138 [ @ 0+0x0
SOS_Task::PreWait+176 [ @ 0+0x0
Ordinal1132+9ab [ @ 0+0x0
Ordinal1391+b8e [ @ 0+0x0
Ordinal429+c4e [ @ 0+0x0
Ordinal319+966 [ @ 0+0x0
0x000007FEF20B04E2
0x000007FEF20B0D27
0x000007FEF20B1ACC
0x000007FEF12204C1
0x000007FEF123A54B
0x000007FEF1239C84
0x000007FEF125DC2F
0x000007FEF1254EC7
SOS_Task::Param::Execute+21e [ @ 0+0x0
SOS_Scheduler::RunTask+a8 [ @ 0+0x0
SOS_Scheduler::ProcessTasks+29a [ @ 0+0x0
SchedulerManager::WorkerEntryPoint+261 [ @ 0+0x0
SystemThread::RunWorker+8f [ @ 0+0x0
SystemThreadDispatcher::ProcessWorker+3c8 [ @ 0+0x0
SchedulerManager::ThreadEntryPoint+236 [ @ 0+0x0
BaseThreadInitThunk+d [ @ 0+0x0
RtlUserThreadStart+21 [ @ 0+0x0

Where it should resolve every frame in the call stack and look something like this (the same example with all pdbs in place):

XeSosPkg::wait_info::Publish+138 [ @ 0+0x0
SOS_Task::PreWait+176 [ @ 0+0x0
EventInternal::Wait+1e3 [ @ 0+0x0
FCB::SyncWrite+104 [ @ 0+0x0
DBMgr::CopyModel+fe [ @ 0+0x0
DBMgr::CreateAndFormatFiles+966 [ @ 0+0x0
CStmtCreateDB::CreateLocalDatabaseFragment+682 [ @ 0+0x0
DBDDLAgent::CreateDatabase+f7 [ @ 0+0x0
CStmtCreateDB::XretExecute+8fc [ @ 0+0x0
CMsqlExecContext::ExecuteStmts<1,1>+400 [ @ 0+0x0
CMsqlExecContext::FExecute+a33 [ @ 0+0x0
CSQLSource::Execute+866 [ @ 0+0x0
process_request+73c [ @ 0+0x0
process_commands+51c [ @ 0+0x0
SOS_Task::Param::Execute+21e [ @ 0+0x0
SOS_Scheduler::RunTask+a8 [ @ 0+0x0
SOS_Scheduler::ProcessTasks+29a [ @ 0+0x0
SchedulerManager::WorkerEntryPoint+261 [ @ 0+0x0
SystemThread::RunWorker+8f [ @ 0+0x0
SystemThreadDispatcher::ProcessWorker+3c8 [ @ 0+0x0
SchedulerManager::ThreadEntryPoint+236 [ @ 0+0x0
BaseThreadInitThunk+d [ @ 0+0x0
RtlUserThreadStart+21 [ @ 0+0x0

Note that any < and > symbols won’t show up properly if the call stacks are collected as XML using Extended Events – they will appear as < and >, as in the example above.

Enjoy!