At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!
Course Of The Month
9 days, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL server stopped without any reason
Posted on 2016-08-05
hi,
one of our SQL server running SQL server 2008 with SP4 stopped without any reason and the error from the log is:
1) A user request from the session with SPID 64 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2) SQL Server Assertion: File: <qxcntxt.cpp>, line=1052 Failed Assertion = 'cref == 0'. 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
3) SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).
4) Database Engine Instance=SQL01-CDS;Mail PID=8544;Error Message:
1) Exception Information
===================
Exception Type: System.Data.SqlClient.SqlE
Errors: System.Data.SqlClient.SqlE
Class: 11
LineNumber: 0
Number: -2
Procedure:
Server: 192.168.108.6,1435
State: 0
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Data: System.Collections.ListDic
TargetSite: Void OnError(System.Data.SqlCli
HelpLink: NULL
StackTrace Information
===================
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at Microsoft.SqlServer.Manage
at Microsoft.SqlServer.Manage
at Microsoft.SqlServer.Manage
one of our SQL server running SQL server 2008 with SP4 stopped without any reason and the error from the log is:
1) A user request from the session with SPID 64 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2) SQL Server Assertion: File: <qxcntxt.cpp>, line=1052 Failed Assertion = 'cref == 0'. 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
3) SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).
4) Database Engine Instance=SQL01-CDS;Mail PID=8544;Error Message:
1) Exception Information
===================
Exception Type: System.Data.SqlClient.SqlE
Errors: System.Data.SqlClient.SqlE
Class: 11
LineNumber: 0
Number: -2
Procedure:
Server: 192.168.108.6,1435
State: 0
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Data: System.Collections.ListDic
TargetSite: Void OnError(System.Data.SqlCli
HelpLink: NULL
StackTrace Information
===================
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at Microsoft.SqlServer.Manage
at Microsoft.SqlServer.Manage
at Microsoft.SqlServer.Manage
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
16
8
2- +1
27 Comments
Active 4 days ago
You may need to collect all the logs and dump files and send it to Microsoft support. We can't do much with the information you provided.
Active today
Once you have collected the error log dumps, please try to do the following:
1. Ensure that the system time is synchronized to a central time server and is correct
2. Try to restart the SQL Server Service. If a corruption occurred in the tempdb, restarting the service will reset the tempdb and the service should successfully restart
1. Ensure that the system time is synchronized to a central time server and is correct
2. Try to restart the SQL Server Service. If a corruption occurred in the tempdb, restarting the service will reset the tempdb and the service should successfully restart
Active 4 days ago
our checkdb result returns good :
CHECKDB found 0 allocation errors and 0 consistency errors in database
CHECKDB found 0 allocation errors and 0 consistency errors in database
Active 4 days ago
"Looks like you didn't follow our recommendation to collect the logs and you went to a different path (nobody asked about CHECKDB)."
you mean this :
"You may need to collect all the logs and dump files and send it to Microsoft support."
restart SQL server has been followed and it doesn't help.
you mean this :
"You may need to collect all the logs and dump files and send it to Microsoft support."
restart SQL server has been followed and it doesn't help.
Active 4 days ago
restart SQL server has been followed and it doesn't help.That was only a guess from Nakul in case of tempdb corruption.
I, myself, can't say nothing without getting the result from SQLdiag.
Active today
Is this running on a virtual box? If so, you might want to wait and see if it happens again. I'd also review the servers event logs to see if anything unusual is going on with the server.
As pointed out in the error message:
"this error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. "
The error c0000005 is a memory access violation. The root cause of that was:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
or in other words, the server simply took too long while trying to get data. This was part of a mail process, which might have a bug (in that it had an unhandled exception).
As far as the "fix":
"This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart)."
Already suggested and done.
"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 "
You've also done the DBCC CHECKDB with no errors, so chances are it was timing related. I'd wait to see if it happens again, and if so:
"Contact Product Support Services with the dump produced in the log directory."
or as Vitor said, post the result of SQLDiag.
Jim.
As pointed out in the error message:
"this error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. "
The error c0000005 is a memory access violation. The root cause of that was:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
or in other words, the server simply took too long while trying to get data. This was part of a mail process, which might have a bug (in that it had an unhandled exception).
As far as the "fix":
"This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart)."
Already suggested and done.
"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 "
You've also done the DBCC CHECKDB with no errors, so chances are it was timing related. I'd wait to see if it happens again, and if so:
"Contact Product Support Services with the dump produced in the log directory."
or as Vitor said, post the result of SQLDiag.
Jim.
Active 4 days ago
"Is this running on a virtual box? "
all SQL serve here run under hyper-V .
" I'd also review the servers event logs to see if anything unusual is going on with the server.
"
the log is the one I check from the log.
all SQL serve here run under hyper-V .
" I'd also review the servers event logs to see if anything unusual is going on with the server.
"
the log is the one I check from the log.
Active today
<<the log is the one I check from the log.>>
I meant outside of the posted error; network issues? storage?
Something that might explain why a timeout occurred.
Jim.
I meant outside of the posted error; network issues? storage?
Something that might explain why a timeout occurred.
Jim.
Active 4 days ago
"I meant outside of the posted error; network issues? storage? "
I checked all that and we also have network problem too but the network problem is one day later and it cause replication lag behind.
the SQL server restart one ahead.
I checked all that and we also have network problem too but the network problem is one day later and it cause replication lag behind.
the SQL server restart one ahead.
Active 4 days ago
vitor,
sorry the SQLdiag page is a bit complex and I wan to use the simpiest way to run that command, what syntax you usually use to run that ?
sorry the SQLdiag page is a bit complex and I wan to use the simpiest way to run that command, what syntax you usually use to run that ?
Active 4 days ago
I read all that:
https://www.mssqltips.com/sqlservertip/1863/introduction-to-sqldiag-for-sql-server-performance-monitoring-and-tuning/
https://blogs.msdn.microsoft.com/pamitt/2011/02/25/how-to-use-sqldiag-sqlnexus-and-pal-tools-to-analyze-performance-issues-in-sql-server/
https://msdn.microsoft.com/en-us/library/ms162833(v=sql.105).aspx
actually what XML file you use ? the default one in the same path ?
https://www.mssqltips.com/sqlservertip/1863/introduction-to-sqldiag-for-sql-server-performance-monitoring-and-tuning/
https://blogs.msdn.microsoft.com/pamitt/2011/02/25/how-to-use-sqldiag-sqlnexus-and-pal-tools-to-analyze-performance-issues-in-sql-server/
https://msdn.microsoft.com/en-us/library/ms162833(v=sql.105).aspx
actually what XML file you use ? the default one in the same path ?
Active 4 days ago
I don't even find example .xml file from http://sqlnexus.codeplex.com/
Active 4 days ago
I am sorry, marrowyung. I missed somehow your comments for this question.
actually what XML file you use ? the default one in the same path ?Yes, the default one should be fine.
Active 4 days ago
hi,
yeah, I know there are default one, but usually what is the full command you use for that?
that command page is a bit complex to read.
yeah, I know there are default one, but usually what is the full command you use for that?
that command page is a bit complex to read.
Active 4 days ago
hi all,
these errors just come again:
before that I can only see our idera compliance manger is running and collect trace file.
these errors just come again:
DESCRIPTION: A user request from the session with SPID 164 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
DESCRIPTION: Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.
before that I can only see our idera compliance manger is running and collect trace file.
Active 4 days ago
send all information to idera and see what they said.
SQL server with SP3 don't have that problem but at this moment, it seems the one with SP4 has problem.
we will upgrade it to SQL server 2014 with SP2+ CU1 very soon but experience this, this error already strong enough for us to restart SQL server as error keep coming. we have no choice.
SQL server with SP3 don't have that problem but at this moment, it seems the one with SP4 has problem.
we will upgrade it to SQL server 2014 with SP2+ CU1 very soon but experience this, this error already strong enough for us to restart SQL server as error keep coming. we have no choice.
Active 4 days ago
yes, but I think I will upload to here first.
which link I can send to MS? they cost ?
which link I can send to MS? they cost ?
Active 4 days ago
which link I can send to MS? they cost ?Is the .dmp file. I'm guessing that your company has a support contract with MS. That's usual when you have their products.
Active 4 days ago
I guess now we know why that box stop without any reason as we are setting up Idera compliance manager (CM) to audit a SQL 2008 server with SP4, it doesn't even happen to SP3 at all.
DESCRIPTION: A user request from the session with SPID 207 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
Is caused by CM is the audited SQL server is running SQL server 2008 with SP4 when DML audit is turned on
This one:
DESCRIPTION: Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.
Is caused by CM is the audited SQL server is running SQL server 2008 with SP4 when Before and AFTER audit is on.
DESCRIPTION: A user request from the session with SPID 207 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
Is caused by CM is the audited SQL server is running SQL server 2008 with SP4 when DML audit is turned on
This one:
DESCRIPTION: Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.
Is caused by CM is the audited SQL server is running SQL server 2008 with SP4 when Before and AFTER audit is on.
Featured Post
At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 17 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.