0

Problem:

One of our clients has SQL Server 2005 running on a Windows 2008 R2 Standard machine. Every once in a while, the server fails with the following error:

SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: <local machine>]

The error occurs at a rate of about once per second, with the value for CLIENT: being the only thing that changes (sometimes, instead of <local machine> it shows the IP of the machine or the IP of other machines belonging to the client) and until the SQL Server is restarted, no connections can be made to it. After the restart, it works fine.

The problem happens about once or twice per month. There are no windows logs for the previous occurrence; I've since increased the max size for the Application log.

Machine configuration:

  • OS: Windows 2008 R2 Standard SP1 (x64)
  • SQL: Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 6.1 (Build 7601: Service Pack 1)
  • CPU: Intel Xeon E5430 @ 2.66GHz
  • RAM: 32 GB
  • Paging file: 32 GB on drive E (System managed), None on all other drives (including drive C)

More info:

  • The server has 2 databases that are actively used:
  • One database is used for replication (1 Publication with about 450 subscribers, most of which synchronize daily, usually more than once per day). The same database is also used by a web application that has about 150 subscribers that use it actively during the day.
  • Both of the databases also have frequent jobs running that mainly do file imports and transfers from one db to the other.

Update:

While checking the logs once again, I've noticed that the AppDomain gets marked for unload due to memory pressure, unloaded and recreated at a rate of about once every 30 minutes. During the last 2 occurences of the stated problem, the AppDomain went up to 250 and 264, respectively. Could this be a related issue?

Community
  • 1
  • 1
ovidiufelixb
  • 45
  • 3
  • 11
  • You would get that if your application server, or clients if they directly access the database, doesn't close connections properly. If you ask me, the problem is not with SQL Server but in your application. – TT. Feb 22 '16 at 11:24
  • How can I check to see if that's the case? What should I be looking for? – ovidiufelixb Feb 22 '16 at 12:06
  • Voted to move to dba.stackexchange – TT. Feb 24 '16 at 19:17

1 Answers1

0

This error could be due to a max worker threads setting that is too low. You can set this as:

EXEC sp_configure 'max worker threads',0
GO
RECONFIGURE WITH OVERRIDE
GO

to raise the limit.

It's entirely possible that you are getting the error due to having too many connections open, in other words the error is the symptom rather than the cause. You should review your application(s) for proper closing of connections.

You can inspect all open connections in SQL Server using sp_who:

Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.

More information on how to inspect open connections, read this thread on SO.

Community
  • 1
  • 1
TT.
  • 15,774
  • 6
  • 47
  • 88
  • Running the error through a search engine lists a lot of similar threads, best to review those as well. – TT. Feb 22 '16 at 13:04
  • Unfortunately, the _max worker threads_ option is already set to 0. – ovidiufelixb Feb 23 '16 at 07:44
  • I haven't had much luck searching the error in other threads. Most of the threads I've found on the web do not address this specific error, but instead talk about other errors that some people seem to get at about the same time as this one. Other threads suggest disabling the SYN flood protection via registry modifications, but this option doesn't apply to Windows Server 2008 R2. – ovidiufelixb Feb 23 '16 at 08:05
  • @ovidiufelixb Your next best bet is to **review** all software components for proper **closing** of connections. Don't just point at SQL Server, make sure that your software isn't the culprit. – TT. Feb 23 '16 at 08:12
  • While checking the logs once again, I've noticed that the AppDomain gets marked for unload due to memory pressure, unloaded and recreated at a rate of about once every 30 minutes. During the last 2 occurences of the stated problem, the AppDomain got up to 250 and 264, respectively. Could this be a related issue? Regarding the high number of connections, are there any guidelines to this? What does having _too many_ open connections mean? Could such a problem be caused by deadlocks (there have been reports of that)? Is there an easy way to monitor (or log) the number of connections? – ovidiufelixb Feb 23 '16 at 08:15
  • @ovidiufelixb Memory pressure can be a possible cause too. – TT. Feb 23 '16 at 08:21
  • @ovidiufelixb So when you get this error, and you run `sp_who` do you get a sense of the number of connections that are open, and who's got them opened? Since you cannot connect when SQL Server goes in error mode, you best leave a connection open using SSMS so you can fire that stored procedure when you need to. – TT. Feb 24 '16 at 21:34