Symptoms
During Microsoft SQL Server startup, you notice one or more of the following symptoms immediately after database recovery is complete and client connections are enabled.
Symptom 1
You receive error messages and assertions that resemble the following in your SQL Server error log:
2014-12-13 08:03:34.85 spid24s Using 'dbghelp.dll' version '4.0.5'2014-12-13 08:03:34.85 spid24s **Dump thread - spid = 0, EC = 0x0000000082274B202014-12-13 08:03:34.85 spid24s ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\LOG\SQLDump0001.txt2014-12-13 08:03:34.85 spid24s * *******************************************************************************2014-12-13 08:03:34.85 spid24s *2014-12-13 08:03:34.85 spid24s * BEGIN STACK DUMP:2014-12-13 08:03:34.85 spid24s * 12/13/14 08:03:34 spid 242014-12-13 08:03:34.85 spid24s *2014-12-13 08:03:34.85 spid24s * Location: ghost.cpp:17422014-12-13 08:03:34.85 spid24s * Expression: tcln1 != NULL2014-12-13 08:03:34.85 spid24s * SPID: 242014-12-13 08:03:34.85 spid24s * Process ID: 354442014-12-13 08:03:34.85 spid24s *2014-12-13 08:03:35.47 spid24s Error: 17066, Severity: 16, State: 1.2014-12-13 08:03:35.47 spid24s SQL Server Assertion: File: <ghost.cpp>, line=1742 Failed Assertion = 'tcln1 != NULL'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
Symptom 2
You receive error messages and exceptions that resemble the following in your SQL Server error log:
2014-12-13 12:38:30.25 spid51 Using 'dbghelp.dll' version '4.0.5'2014-12-13 12:38:30.25 spid51 ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\LOG\SQLDump0003.txt2014-12-13 12:38:30.25 spid51 SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.2014-12-13 12:38:30.25 spid51 * *******************************************************************************2014-12-13 12:38:30.25 spid51 *2014-12-13 12:38:30.25 spid51 * BEGIN STACK DUMP:2014-12-13 12:38:30.25 spid51 * 12/13/14 12:38:30 spid 512014-12-13 12:38:30.25 spid51 *2014-12-13 12:38:30.25 spid51 *2014-12-13 12:38:30.25 spid51 * Exception Address = 000000000030D47C Module(sqlservr+00000000000FD47C)2014-12-13 12:38:30.25 spid51 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION2014-12-13 12:38:30.25 spid51 * Access Violation occurred reading address FFFFFFFFFFFFFFFF2014-12-13 12:38:30.25 spid51 * Input Buffer 54 bytes -2014-12-13 12:38:30.25 spid51 * exec usp_select12014-12-13 12:38:30.77 Server Error: 17310, Severity: 20, State: 1.2014-12-13 12:38:30.77 Server A user request from the session with SPID 51 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.The Access Violation will have the following call stack:sqlservr!TaskGhostCleanup::IsHashed+0x8dsqlservr!TaskGhostCleanup::Enqueue+0x32sqlservr!IndexRowScanner::MoveToRowOnNextPage+0x9csqlservr!IndexDataSetSession::GetNextRowValuesInternal+0x11cb
Symptom 3
After you receive the messages that are mentioned in the previous symptom sections, you receive the following messages in the SQL Server error log:
2014-12-13 08:04:53.37 Server Process 0:0:0 (0x23c8) Worker 0x000000002880C1A0 appears to be non-yielding on Scheduler 23. Thread creation time: 13062953007877. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 0%. System Idle 88%. Interval: 70013 ms.2014-12-13 08:04:53.37 Server Process 0:0:0 (0x71d8) Worker 0x000000002A8D21A0 appears to be non-yielding on Scheduler 30. Thread creation time: 13062953007891. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 0%. System Idle 88%. Interval: 70013 ms.2014-12-13 08:04:53.38 Server ***Unable to get thread context for spid 02014-12-13 08:04:53.38 Server * *******************************************************************************2014-12-13 08:04:53.38 Server *2014-12-13 08:04:53.38 Server * BEGIN STACK DUMP:2014-12-13 08:04:53.38 Server * 12/13/14 08:04:53 spid 294882014-12-13 08:04:53.38 Server *2014-12-13 08:04:53.38 Server * Non-yielding Scheduler2014-12-13 08:04:53.38 Server *2014-12-13 08:04:53.38 Server * *******************************************************************************2014-12-13 08:04:53.38 Server Stack Signature for the dump is 0x00000000000003412014-12-13 08:04:55.43 Server External dump process return code 0x20000001. External dump process returned no errors.2014-12-13 08:04:55.43 Server Process 0:0:0 (0x9358) Worker 0x0000000081CE41A0 appears to be non-yielding on Scheduler 4. Thread creation time: 13062953009701. Approx Thread CPU Used: kernel 0 ms, user 15 ms. Process Utilization 0%. System Idle 88%. Interval: 70011 ms.
SQL Server may be unresponsive to user requests at this point. If this is the case, you must restart the service to correct the situation.
Cause
This problem occurs because user queries try to use the ghost cleanup queues before this process is fully initialized.
Resolution
Service pack information
To resolve this issue, obtain Service Pack 1 for SQL Server 2014.
For more information about SQL Server 2014 Service Pack 1 (SP1), see bugs that are fixed in SQL Server 2014 Service Pack 1.
Hotfix for SQL Server 2008 SP4
To resolve this issue, apply KB 3034373: An on-demand hotfix update package is available for SQL Server 2008 SP4.
Hotfix for SQL Server 2008 R2 SP3
To resolve this issue, apply KB 3033860: An on-demand hotfix update package is available for SQL Server 2008 R2 SP3.
Cumulative update information
The feature improvement was introduced in the following cumulative update of SQL Server.
Cumulative Update 6 for SQL Server 2014 /en-us/help/3031047
Cumulative Update 4 for SQL Server 2012 SP2 /en-us/help/3007556
Cumulative Update 14 for SQL Server 2012 SP1 /en-us/help/3023636
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Workaround
To work around this problem, follow these steps:
-
Configure -T669 as startup parameter. This trace flags prevents user queries from queuing requests to the ghost cleanup process.
-
Set up a SQL Server Agent alert to trigger a job on SQL Msg 3408. For example, set up the following alert:
Recovery is complete. This is an informational message only. No user action is required.
-
Inside this job, run a TSQL script to wait for 5 to 10 minutes, and then run the DBCC TRACEOFF (669,-1) command.
This procedure makes sure that this trace flag is active only during SQL Server startup. The use of this trace flag does not affect the usual functioning of the background ghost cleanup process.
Status
Microsoft has confirmed that this is a problem with SQL Server and is currently investigating a fix for this issue. This Knowledge Base article will be updated with additional information as it becomes available.
References
Inside the Storage Engine: Ghost cleanup in depth Alerts sp_add_alert (Transact-SQL) DBCC TRACEOFF (Transact-SQL) Trace Flags Database Engine Startup Options
