Background

Received an alert indicating that one of our database jobs is lagging.

TroubleShooting

Adam Machanic

sp_whoisActive

Ran Adam Machanic’s sp_whoIsActive ..

And, noticed that a couple of Restore Transaction Log sessions have been running for 6 to 7 hours.

SQL Server – Error Log Folder

Reviewed the errorlog folder and noticed a couple of mdmp files.

 

Secure mdmp files

Secured the mdmp files and copied them locally to my machine.

Visual Studio

Visual Studio Community Edition 2017

About

Have a copy of the free community edition of SQL Server 2017.

Launched it.

Access mdmp files

Using menu item File \ File Open accessed the “Open File” window.

For file types chose “Dump files ( dmp, mdmp ) ”

Dump – SQLDump0065.mdmp

Image

Explanation

1. Process Information :- E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
   • MSSQL12.MSSQLSERVER
     • Version :- MS SQL Server v2014
2. Process Architecture :- x64
3. Exception Code :- 0xC0000005
4. Exception Information :- The thread tried to read or write to a virtual address for which it does not have the appropriate access.

 

Dump – SQLDump0066.mdmp

Image

Explanation

1. Process Information :- E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
   • MSSQL12.MSSQLSERVER
     • Version :- MS SQL Server v2014
2. Process Architecture :- x64
3. Exception Code :- 0x000042AC
4. Exception Information :- {blank}

 

Summary

Quick Points here:

1. Alerts & Notifications
   • Have alerts and notifications on jobs
   • Have them set up to email a distribution list or yourself
   • Need and have a smart phone
2. TroubleShoot
   • Prepare and have diagnostic checklist & steps
   • Review Error Logs
   • Dump Files?
     • If dump files are being generated, analyze them

 

Reference

1. Microsoft
   • Windows Development Center
     • DirectX Graphics and Gaming > Classic DirectX Graphics > Technical Articles
       • Crash Dump Analysis
         Link
   • SQL Server Customer Advisory Team
     • Thomas Kejser
       • Looking deeper into SQL Server using Minidumps
         Link