SQL Server timing out

Question

SQL Server timing out

SUBRAHMANYA HEDGE

SSCommitted

Points: 1514
More actions
August 20, 2015 at 9:15 am

#307028

We are getting SQL Server time-outs in one of our SQL Server. SQL Server will not respond to any of the operations; not even connection. And the error we can see are only these -
System Log:
[highlight=#ffff11]A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSSQLSERVER service.
[/highlight]
SQL Error Log before crash:
[highlight=#ffff11]Unsafe assembly 'encryption, version=4.1.0.0, culture=neutral, publickeytoken=aab428146cd0877e, processorarchitecture=msil' loaded into appdomain 2 (databasename.dbo[runtime].1).[/highlight]
And the only option left is to restart the SQL Service. And even the SQL Service won't stop properly and 7024 event get generated for SQL Service and SQL Agent. Also SQL service start won't happen properly and we see below errors for a while (say 5 minutes).
initerrlog: Could not open error log file 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'. Operating system error = 32(failed to retrieve text for this error. Reason: 15105).
This error looks like an issue with permission; but if we try after 5 minutes, SQL service starts without any issue.
Server Details:
OS: Windows Server 2008 R2 Standard
SQL: SQL Server 2008 SP2 Standard Edition
Has anyone ever came across such scenario?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

SUBRAHMANYA HEDGE (8/20/2015)
We are getting SQL Server time-outs in one of our SQL Server. SQL Server will not respond to any of the operations; not even connection. And the error we can see are only these -
System Log:
[highlight=#ffff11]A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSSQLSERVER service.
[/highlight]
SQL Error Log before crash:
[highlight=#ffff11]Unsafe assembly 'encryption, version=4.1.0.0, culture=neutral, publickeytoken=aab428146cd0877e, processorarchitecture=msil' loaded into appdomain 2 (databasename.dbo[runtime].1).[/highlight]
And the only option left is to restart the SQL Service. And even the SQL Service won't stop properly and 7024 event get generated for SQL Service and SQL Agent. Also SQL service start won't happen properly and we see below errors for a while (say 5 minutes).
initerrlog: Could not open error log file 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'. Operating system error = 32(failed to retrieve text for this error. Reason: 15105).
This error looks like an issue with permission; but if we try after 5 minutes, SQL service starts without any issue.
Server Details:
OS: Windows Server 2008 R2 Standard
SQL: SQL Server 2008 SP2 Standard Edition
Has anyone ever came across such scenario?

Looks like you have an unsafe CLR assembly running maybe? Would help a LOT is you explained what you were doing when this happens.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Operating system error 32 is "File in use by another process". It's not permission-related.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SUBRAHMANYA HEDGE SSCommitted Points: 1514 More actions · Answer 3

Thanks Sean & Gail for responding.

@ Sean - We were not sure what was running and we couldn't set a pattern too. And we are checking with application team on the possible transactions running at that time. Now, we are running profiler trace and perfmon to capture more info if any.

@Gail - Thanks; this server is recently built and antivirus exclusions are not set on database paths. So one of the theory is that virus checker passed through during the crash window. It's just a theory and we don't have anything substantial to prove it.

Can this be a reason?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Yes. It's a common reason for error 32.

Fix your virus checker according to MS recommendations: https://support.microsoft.com/en-us/kb/309422

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SUBRAHMANYA HEDGE SSCommitted Points: 1514 More actions · Answer 5

GilaMonster (8/21/2015)
Yes. It's a common reason for error 32.
Fix your virus checker according to MS recommendations: https://support.microsoft.com/en-us/kb/309422

Thanks !!! We have already asked Security team to exclude SQL folders (SQL Bits, data and log files and backup folders) from virus scanning. Nothing happened in last 12 hours.... 🙂