SQL Dump Analysis & Troubleshooting

When SQL experiences a serious issue it will often dump the contents of its memory to disk. This is known as a memory dump or crash dumps.

Default file location of dumps in SQL Server:

By default, Dump directory is located at 'C:\Microsoft SQL Server\MSSQL15. MSSQLSERVER\MSSQL\LOG\' in Windows system.
If you want to change the dump directory of SQL Server dump files (.mdmp extension) from the default location. You can do this using “SQL Server Configuration Manager”

1. Start SQL Server Configuration Manager
2. Right Click on SQL Server Instance and select Properties
3. Go to Advanced Tab and change Dump Directory

sys.dm_server_memory_dumps

It contains the information about memory dumps files generated by the SQL Server Database Engine. It returns 3 columns listed as below:

SELECT *

FROM sys.dm_server_memory_dumps

Analyzing Dump Files using WinDBG:

WinDBG (Windows DeBuGger) is an analytic tool used for analysing and debugging Windows crash dumps, also known as BSODs (Blue Screens of Death). It is free to download from Microsoft and is used by the vast majority of debuggers.

WinDBG requires .NET Framework 4.6 in order to run.

We have two dump files from SQL Server:

Open dump file using WinDBG:

File 1:

Launch Windbg and Choose File menu and then open the crash dump file SQLDump0014.mdump

Click on !analyze -v to get more detailed information about dump file:

Non-Yielding IOCP Listener means that the thread that handles the IO completion routines took a long time doing something, and may stuck or hung.

Solution:

• Install latest Cumulative Updates for SQL Server.
• Identify the .dll and verify with the vendor what it was doing.
• Uninstall the application that causes issues.
• Restart Operating System & Application.

------------------------------------------------------------------------------

File 2: 

Open the crash dump file SQLDump0019.mdump

Click on !analyze -v to get more detailed information about dump file:

Non-yielding resources usually mean that something pre-emptive was executing which could be CLR, an XP, a OLE Automation Call, or it could be a real bug in SQL Server, that you've happened upon in your configuration.

This means that there is a background thread that keeps running and making other worker threads yielding back, in time.

Resolution:

• Install cumulative update of SQL Server.
• Another solution is to apply patch.

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-server-memory-dumps-transact-sql?view=sql-server-ver15

https://support.microsoft.com/en-in/help/4048942/fix-stalled-iocp-listener-and-non-yielding-iocp-listener-memory-dumps