SQLDumper unable to generate mdmp files in SQL Server 2008 Failover clusters
SQLDumper utility is used by various components of the product to generate and save diagnostic information in the form of mini-dump and other log files. You will normally find the output files [with extension .mdmp, .txt and .log] created by sqldumper.exe in the LOG folder of the specific instance for the Database Engine. Recently while working with a customer we noticed a problem that prevents these important files from getting created and could affect some of you who are managing SQL Server 2008 failover cluster installations. There are 2 alternate options you can follow to install SQL Server failover cluster:
Integrated Installation with Add Node
Advanced/Enterprise Installation [Prepare and Complete]
If you perform a “Prepare and Complete” option of install for a failover cluster, then sqldumper.exe may not have the capability to automatically generate dumps for critical server health conditions including exceptions and other errors.
This problem is caused by a combination of two factors:
ErrorDumpDir registry value under the key [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\CPE] is pointing to an incorrect location
SQL Server service account and SQLDumper.exe does not have necessary permissions to write files here due to the ACL settings on this folder location
Under this condition, when SQLSERVR.EXE attempts to launch SQLDUMPER.EXE and generate dumps for sever conditions or events; you will notice the following in the SQL Server Error log.
2009-02-25 23:01:03.40 spid52 Using 'dbghelp.dll' version '4.0.5'
2009-02-25 23:01:03.40 spid52 **Dump thread - spid = 0, EC = 0x0000000080AA40F0
2009-02-25 23:01:03.40 spid52 *
2009-02-25 23:01:03.40 spid52 * User initiated stack dump. This is not a server exception dump.
2009-02-25 23:01:03.40 spid52 *
2009-02-25 23:01:03.40 spid52 * *******************************************************************************
2009-02-25 23:01:03.40 spid52 *
2009-02-25 23:01:03.40 spid52 * BEGIN STACK DUMP:
2009-02-25 23:01:03.41 spid52 * 02/25/09 23:01:03 spid 52
2009-02-25 23:01:03.41 spid52 *
2009-02-25 23:01:03.41 spid52 * Dump triggered by event 'sqldumper_check'.
2009-02-25 23:01:03.41 spid52 *
2009-02-25 23:01:03.41 spid52 * Input Buffer 64 bytes -
2009-02-25 23:01:03.41 spid52 * select * from notable
2009-02-25 23:01:03.41 spid52 *
2009-02-25 23:01:03.41 spid52 * *******************************************************************************
2009-02-25 23:01:03.41 spid52 * -------------------------------------------------------------------------------
2009-02-25 23:01:03.41 spid52 * Short Stack Dump
2009-02-25 23:01:03.43 spid52 Stack Signature for the dump is 0x000000000C2C26CB
2009-02-25 23:01:03.49 spid52 External dump process return code 0x20000003.
Error - Failed to create dump file.
If you monitor the sequence of operations performed by sqldumper.exe through Process Monitor tool, you can clearly see the problem:
11:01:03.5060825 PM SQLDUMPER.EXE 1704 CreateFile C:\Program Files\Microsoft SQL Server\MSSQL10.MYINSTANCENAME\MSSQL\LOG SUCCESS Desired Access: Read Data/List Directory, Synchronize, Disposition: Open, Options: Directory, Synchronous IO Non-Alert, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: Opened
11:01:03.5061132 PM SQLDUMPER.EXE 1704 QueryDirectory C:\Program Files\Microsoft SQL Server\MSSQL10.MYINSTANCENAME\MSSQL\LOG\SQLDmpr*.mdmp NO SUCH FILE Filter: SQLDmpr*.mdmp
11:01:03.5061420 PM SQLDUMPER.EXE 1704 CloseFile C:\Program Files\Microsoft SQL Server\MSSQL10.MYINSTANCENAME\MSSQL\LOG SUCCESS
11:01:03.5062758 PM SQLDUMPER.EXE 1704 CreateFile C:\Program Files\Microsoft SQL Server\MSSQL10.MYINSTANCENAME\MSSQL\LOG\SQLDMPR0001.MDMP NAME NOT FOUND Desired Access: Read Attributes, Disposition: Open, Options: Complete If Oplocked, Attributes: N, ShareMode: Read, Write, Delete, AllocationSize: n/a
11:01:03.5063605 PM SQLDUMPER.EXE 1704 CreateFile C:\Program Files\Microsoft SQL Server\MSSQL10.MYINSTANCENAME\MSSQL\LOG\SQLDmpr0001.mdmp ACCESS DENIED Desired Access: Generic Read/Write, Disposition: Create, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: 0
<< cut >>
11:01:03.5072883 PM SQLDUMPER.EXE 1704 CreateFile C:\Program Files\Microsoft SQL Server\MSSQL10.MYINSTANCENAME\MSSQL\LOG\SQLDUMPER_ERRORLOG.log ACCESS DENIED Desired Access: Generic Write, Read Attributes, Disposition: OpenIf, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: 0
11:01:03.5073584 PM SQLDUMPER.EXE 1704 QueryNameInformationFile C:\Windows\System32 SUCCESS Name: \Windows\System32
11:01:03.5074458 PM SQLDUMPER.EXE 1704 CreateFile C:\Windows\System32\SQLDUMPER_ERRORLOG.log ACCESS DENIED Desired Access: Generic Write, Read Attributes, Disposition: OpenIf, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: 0
11:01:03.5076866 PM SQLDUMPER.EXE 1704 CreateFile C:\Program Files\Microsoft SQL Server\MSSQL10.MYINSTANCENAME\MSSQL\LOG\SQLDUMPER_ERRORLOG.log ACCESS DENIED Desired Access: Generic Write, Read Attributes, Disposition: OpenIf, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: 0
11:01:03.5077526 PM SQLDUMPER.EXE 1704 QueryNameInformationFile C:\Windows\System32 SUCCESS Name: \Windows\System32
11:01:03.5078319 PM SQLDUMPER.EXE 1704 CreateFile C:\Windows\System32\SQLDUMPER_ERRORLOG.log ACCESS DENIED Desired Access: Generic Write, Read Attributes, Disposition: OpenIf, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, Write, AllocationSize: 0
11:01:03.5078995 PM SQLDUMPER.EXE 1704 Thread Exit SUCCESS Thread ID: 3256, User Time: 0.0000000, Kernel Time: 0.0156250
11:01:03.5080350 PM SQLDUMPER.EXE 1704 Process Exit SUCCESS Exit Status: 536870915, User Time: 0.0000000, Kernel Time: 0.0156250, Private Bytes: 2,822,144, Peak Private Bytes: 2,822,144, Working Set: 4,042,752, Peak Working Set: 4,042,752
After you perform a “Prepare and Complete” SQL Failover cluster setup, you will observe the following settings:
Registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MYINSTANCENAME\CPE
ErrorDumpDir = C:\Program Files\Microsoft SQL Server\MSSQL10.MYINSTANCENAME\MSSQL\LOG\
Folders:
Directory of C:\Program Files\Microsoft SQL Server\MSSQL10.MYINSTANCENAME\MSSQL
02/25/2009 08:35 PM <DIR> .
02/25/2009 08:35 PM <DIR> ..
02/25/2009 08:35 PM <DIR> Binn
02/25/2009 08:35 PM <DIR> Install
02/25/2009 03:44 PM <DIR> LOG
02/25/2009 03:39 PM <DIR> Upgrade
01/08/2009 09:55 PM 14,168 sql_engine_core_inst_keyfile.dll
07/10/2008 04:40 AM 17,432 sql_fulltext_keyfile.dll
Directory of H:\MSSQL10.MYINSTANCENAME\MSSQL
02/25/2009 05:05 PM <DIR> .
02/25/2009 05:05 PM <DIR> ..
02/25/2009 05:05 PM <DIR> Backup
02/25/2009 05:05 PM <DIR> DATA
02/25/2009 08:37 PM <DIR> FTData
02/25/2009 05:05 PM <DIR> JOBS
02/25/2009 10:52 PM <DIR> Log
02/25/2009 05:05 PM <DIR> repldata
H drive is the shared cluster disk drive that is part of the cluster group configured for this sql failover cluster instance. C drive is the local drive of the node.
Notice that the registry key ErrorDumpDir is pointing to the LOG folder in the C drive. If you examine the LOG folder in both the drives, you will notice that the error logs are present in LOG folder of the H drive. The LOG folder in the C drive will be empty.
The Prepare phase of the setup creates this LOG folder in the C drive and configures the CPE registry key to this folder. This LOG folder of C drive is not setup with correct ACL’s so SQL Server service account can create files in this folder. The Complete phase does not remap this registry key value to the LOG folder in the shared cluster drive. That is how we end up with this scenario.
CURRENT SOLUTION
Go to Start >> All programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Error and Usage Reporting.
Once the "Error and usage Report Settings" dialog opens, click on the OPTIONS button. This will list out all the Instances and Components of SQL Server Products installed on this cluster. You can edit the “Error Reports Location” for the “SQL Database Engine” component. This will store the folder location in the ErrorDumpDir value in the registry key [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\CPE]. Change this folder location to point to the LOG folder where the SQL Server error logs are currently located. After making this change you will need to restart the SQL Server Resource for this new setting to take effect. This registry key is read and the value stored only during SQL Server startup.
CURRENT STATUS
SQL Server Development team is investigating this issue. We will update this post when this issue is resolved.
SCRIPT TO TEST SQLDUMPER
You can use the following script to test if SQL Server can successfully launch SQLDumper utility and generate the dump files to the correct LOG folder. Execute this script only during a period of very low activity to avoid any interruptions.
-- setup a Extended Event session to generate dumps when a specific error is encountered
create event session sql_dumper_test on server
add event sqlserver.error_reported (
action (sqlserver.create_dump_single_thread)
where error = 2627 and package0.counter = 1) --Violation of PRIMARY KEY constraint
go
alter event session sql_dumper_test on server state=start
go
-- generate the error 2627
create table #sql_dumper_test (testcol int primary key)
go
insert into #sql_dumper_test values ( 1 )
go
insert into #sql_dumper_test values ( 1 )
go
drop table #sql_dumper_test
go
-- clean up the Extended Event session
alter event session sql_dumper_test on server state=stop
go
drop event session sql_dumper_test on server
go
After executing this script, review the SQL Server Error log. Examine the LOG folder to check if new mdmp file was created.
Thanks
Suresh B. Kandoth
