Summary: This article discusses common way to prevent SQL dump from being generated in different exception scenarios. This workaround serves for hot scenarios where you need to immediately stop the PRODUCTION outage caused by dump generation. You should already have a dump file feasible for RCA investigation purpose. You should also have confirmed that the exactly same exception continue triggering dump which has the same call stack for the offended thread.
In a hot PRODUCTION environment , especially in SQL Clustered setting (SQL AG or FCI), to quickly safe your production from outage caused by dump generation process, we can sometimes consider disabling the dump generation process. The drawback is that we will not get the dump file when exception happens for further troubleshooting purpose. Also, preventing dump generation cannot prevent the SQL exception from happening. Be careful of the risks when you adopt this method and ensure you have discussed with the related parties before implementing the changes.
Of course, increasing lease timeout and health check timeout can be another feasible option but in some PRODUCTION environment which requires fast response time and quick failover on disaster(they don't want to wait for dump generation before failover), such option is not accepted.
Below are a few trace flags to prevent dump generation in common scenarios. For other scenarios, you can refer to our internal document for related trace flags and behaviors.
Note: Be careful of the risks when you adopt any of below methods. Ensure you have discussed with the related parties before implementing the changes.
1. Prevent non-yielding dump from being generated. Also, with below 3 trace flags especially 2542 enabled, you are expected to see other SQL exception dump in common scenarios to be prevented since TF2542 will skip the dump generation process. However, in edge case or special scenario, you may still see SQL dump being generated with all these 3 trace flags enabled. Refer to other trace flags discussed on this document or in our internal document for resolutions.
|
1260 |
Disable minidump for all schedmon conditions |
Startup|DBCC |
|
3646 |
'Do not create symptom dumps |
Startup|DBCC |
|
2542 |
'Skip the minidump in sqldumper.exe' |
Startup|DBCC |
2. Prevent index corruption dump from being generated
|
11063 |
'Do not produce dumps when a query is aborted due to failure to locate a row in an index' |
Startup|DBCC |
3. Prevent latch timeout dump from being generated
|
837 |
'Never produce dumps on latch timeouts' |
Startup|DBCC |
4. Only generated 1 dump file for dump trigger
|
8026 |
'Clear dump trigger once it was used' |
Startup|DBCC |
5. Limit the maximum number of SQL dump in registry key
There are some registry keys to set the dump limits . By default it is set to (0) indicating INFINITE. Changing it to 1 set the maximum number of dump to one. The count will be reset on SQL Server service restart.
Under the Setup base key location: (EX: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\Setup) -- change the path with your installation
Please create the following keys:
• MaxDumps (DWORD) value à put 1
• MaxFullDumps (DWORD) value à put 1
DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
Posted at https://sl.advdat.com/3kEBh4m