Alright, let's tackle your two SQL Server 2016 issues step by step—first fixing the failed .mdmp dump generation, then covering how to read those dumps.

• Check SQL Server Service Permissions
  The SQL Server service account needs write access to the dump directory (default path for 2016 is usually C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\LOG). If it doesn't have permissions, dumps will fail outright. Check the SQL Server error log for entries mentioning permission denied—those are dead giveaways.

• Verify Dump Configuration Settings
  Fire up SSMS, right-click your server → Properties → Advanced. Double-check the "Dump Directory" is a valid, accessible path. Also confirm the "Dump Type" (full, mini, etc.) and "Error Reporting" settings are configured as intended. For full memory dumps, ensure the target drive has enough space—these dumps can be as large as your SQL Server's allocated memory.

• Inspect Disk Space
  Full .mdmp dumps are massive because they capture the entire SQL Server process memory. If the drive holding the dump directory is low on free space, the generation will fail. Clear up space or switch the dump path to a drive with more room if needed.

• Check for Unstable SQL Server Processes
  Sometimes dumps fail because SQL Server is already in a bad state—hanging, crashing abruptly, or stuck in a loop. Pop open the Windows Event Viewer's Application log and look for SQL Server-related errors or crash events. These logs will often explain why the dump couldn't be created.

• Test Manual Dump Generation
  If automatic dumps aren't working, try creating one manually to narrow down the issue:

  1. Open Task Manager, locate the sqlservr.exe process.
  2. Right-click it → Create Dump File.
  3. If this fails, the problem is system-level (permissions, disk, process corruption). If it works, the issue lies with SQL Server's automatic dump configuration.

• Use WinDbg (Debugging Tools for Windows)
  WinDbg is the go-to tool for analyzing .mdmp dumps. Here's a quick start:

  1. Install WinDbg via the Windows SDK—make sure to select the "Debugging Tools for Windows" component during setup.
  2. Open WinDbg, go to File → Open Crash Dump, and select your .mdmp file.
  3. Load SQL Server symbols: Run .symfix to set the symbol path, then .reload /f to load the necessary symbols.
  4. Run !analyze -v for a verbose breakdown of the dump—this will show you the call stack, exception details, and potential root causes like bad stored procedures or memory leaks.

• Correlate with SQL Server Error Logs
  While SSMS can't open .mdmp files directly, you can pair the dump's timestamp with entries in the SQL Server error log. The log will usually have details about what triggered the dump—like an assertion failure, access violation, or deadlock.

• Submit to Microsoft Support (If Needed)
  If WinDbg feels overwhelming, you can submit the dump file to Microsoft's support team via a ticket. Their engineers have specialized tools and deep SQL Server expertise to diagnose issues you might miss.

• Essential WinDbg Commands for SQL Server Dumps
  Keep these commands handy for deeper analysis:

  • !analyze -v: Generates a full, detailed analysis of the dump.
  • !sqlservr.sqlstack: Shows SQL Server-specific call stacks to pinpoint where the issue occurred.
  • !sqlservr.memorydump: Breaks down memory usage at the time of the dump.
  • !threads: Lists all active SQL Server threads when the dump was taken.

内容的提问来源于stack exchange，提问作者user145248