The actual text of the error message was:
DBCC CHECKDB (<Database name>) WITH all_errormsgs, no_infomsgs, physical_only, tableresults executed by SQLLaptopSQLSoldier terminated abnormally due to error state 6. Elapsed time: 0 hours 0 minutes 30 seconds.
To make a long story short, I was testing a pre-beta version of a utility to make running DBCC CHECKDB easy for DBAs who are new to the process (more on this later after I help them work out the bugs). It seemed to work really well for small databases but would crash for large database. I noticed a pattern as the time for the error message was always 30 seconds into running CHECKDB. It seemed obvious to that the utility was using the default query timeout value of 30 seconds and killing CHECKDB at the 30 second point.
So the cause of my abnormal termination due to error code 6 was that the process was cancelled by the end user. I was able to confirm this by manually killing a running CHECKDB both by just clicking cancel in the query window and by running the KILL command in another window. It generated the same error though with a much smaller time frame than 30 seconds.
I can’t say that this is the only scenario that would generate this particular error, but this is definitely one cause that consistently generates the error.
Lonny (@sql_handle)
http://support.microsoft.com/kb/926070/en-us
Error state 6 is sometimes associated with sparse file errors for the internal snapshot used by dbcc checkdb.
SQLSoldier
Lots of things have an error state of 6. What you are taking about is a completely different error. Them having the same state is coincidental only, No reason to think that and this is related.
Lonny
Yeah… reading comprehension issue on my part 🙂 Rereading and separating from the sparse table errors I’m seeing at a site makes things a little more clear 🙂 Fradensql saw similar a few months ago… 10 minute timeout from client. http://www.fradensql.com/2013/03/checkdb-error-state-6/
Marc Crane
SSMS (Microsoft SQL Server Managment Studio)
Right Click Instance node in Object Explorer.
Click Properties
Click Connections
Bottom left: Allow remote connections to this server is CHECKED.
Remote query timeout (in seconds, 0 = no timeout)
Value is set to 600 (10 minutes)
Check this value on the SQL Server and see if it is set to ( 30 )
SQLSoldier
Hi Marc. That setting does not apply here. The remote query timeout setting is for connections that the SQL Server instance initiates to an external source. It does not affect queries coming in to the SQL Server instance.
The query timeout in this case is set at the client via whatever protocol the client application is using. SQL Server will not timeout a query coming in to it. It is the client connection that times out.