First published on MSDN on Mar 05, 2018
Over the years of troubleshooting SQL Server we have come to see the need for manually generating a memory dump of the SQL Server process for in-depth analysis. On several occasions, generating multiple memory dumps at a pre-defined interval has been required in order to see if the state of a particular thread or threads change over time. Yes, it is a long and arduous process to analyze multiple memory dumps, but in those rare cases that has been the only way to successfully discovering the root cause of an issue.
This has necessitated to develop some sort of automation in generating multiple memory dumps. Below is the text of a command file (.BAT or .CMD) that accomplishes this task.
Instructions:
In the below sample file, setting are as follows
@echo off
set /A counter=1
@echo.
@echo.
@echo the application we are generating a dump for is MSSQL$SQL2017
@echo.
@echo.
@echo.
@echo searching for MSSQL$SQL2017: tasklist /FO "TABLE" /FI "SERVICES eq MSSQL$SQL2017"
for /F "tokens=2" %%q IN ('tasklist /FO "TABLE" /FI "SERVICES eq MSSQL$SQL2017" /NH') do @set sqlpid=%%q
@echo.
@echo Process PID = %sqlpid%
:start
@echo generating a mini dump with indirect memory
"c:\Program Files\Microsoft SQL Server\140\Shared\Sqldumper.exe" %sqlpid% 0 0x0128 0 %1
timeout /T 60
set /A counter+=1
@echo counter is %counter%
if %counter% GTR 7 goto end
goto start
:end
@echo Done. Collected memory dumps.
Namaste!
Joseph
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.