SQL Server Performance

SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection

Discussion in 'SQL Server 2005 General DBA Questions' started by amara, Jan 22, 2008.

  1. amara Member

    I am getting this error very often. "SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection"
    At this point of time, we will not be able to connect to the database engine in the SQL Server but I will be able to access all other components like Integration services, Analysis services, reporting services. Why is this issue happening..What is the resolution for this
    Once I restart the server, things will come back to normal
  2. ghemant Moderator

  3. satya Moderator

    What is the level of Service pack on SQL Server?
  4. kzimmerm New Member

    Greetings all;

    Ihaven't heard anything about this issue. None the less I've decided tostart tracking some information on the database server. One thing thatI know is it appears there is a memory leak in sqlservr.exe. I am notsure if this is caused by the ASP ap running against it or if there issomething that runs on the database itself.

    None the less it has been several weeks since the SQL Server serviceswere restarted. All was quiet until last night. When I checked theerror logs this morning it looks like things were in trouble aroundlast night then it cleared itself up by itself. None the lesssqlservr.exe continues to consume memory.

    I had suggested to my boss that we may just want to restart the SQLServer services when sqlservr.exe consumes "x" amount of memory.

    I've put in a very simple tracking system that will allow me to receivean alert via email & text message when this issue occurs again.

    The problem is that the problem still exists. I have bigger projectsto work on than to track down a memory leak. The resolution turns outto be quite simple just by changing our SOP.

    I am watching 4 other SQL Server boxes and it appears this problem is only occuring on 1 box.

    Hopefully someone will have enough time to track down a hotfix that resolves this problem once and forall.

    Kurt
    DBA
    RHWI, Inc
    Poughkeepsie, NY
  5. satya Moderator

    You have to be more clear on the hardware, application software usage and their SERVICE PACK levels, as the latest ones would have few bug fixes.
    In your case restart of SQL Server services is not a final solution, you have to assess the baseline of performance monitoring of your application usage during less traffic hours & busy hours. For more information on Memory refer to http://sqlserver-qa.net/blogs/perftune/archive/tags/memory/default.aspx links
  6. kzimmerm New Member

    Just as a quick rundown on the hardware:
    HP ProLiant DL580 G3
    2 - quad core processors
    16gb memory
    Windows 2003 server SP2
    Sql Server 2005 SP2
    Using 6 web servers running classic ASP.
    Overall database size: 183.8 GB
    1 primary & 1 secondary filegroup
    Majority of traffic occurs between 8:00pm EST to 12:00 midnight EST with up to 2000 concurrent users.
    I have removed the use of the Server Objects of Linked Servers. I had to re-engineer these processes to accomplish the same functionality using either BCP or SSIS. My research lead me to believe this was a primary cause for memory leaks.
    I was also told that the XML parser also exhibits memory leaks but the database doesn't utilize anything along that line.
    I am fully aware of the fact that by restarting the services doesn't solve the problem, but for now resolves any problems during the height of utilization.
    I am short on time resource to dig into the core problem. I was hoping that someone else who might have seen this problem might have been able to provide me a solution.


    Kurt Zimmerman
    DBA
    RHWI, Inc
    Poughkeepsie, NY
  7. SQL_Guess New Member

    Kurt, I am in the same boat.
    Environment:
    SQL 2005 SP2a CU2 Ent Ed 64-bit on Dell 6850, 4x dual core, 16 GB ram, active/passive cluster.
    6 App server, each serving 5 web-servers. Also, 2 replication distribution servers with between 180 and 200 publishers each, using transactional replication and all updating a single table on the DB server.
    at 16:48, we appear to have a cluster issue:
    Date 20/02/2008 16:48:05
    Log Windows NT (Application)
    Source MSSQLSERVER
    Category Failover
    Event 1073760843
    Computer XXXX
    Message
    [sqsrvres] printODBCError: sqlstate = 08S01; native error = 0; message = [Microsoft][SQL Native Client]Communication link failure
    Very soon thereafter, we are completely SPAMMED with:
    Date 20/02/2008 16:50:50
    Log SQL Server (Current - 20/02/2008 16:56:00)

    Source Logon
    Message
    Error: 17189, Severity: 16, State: 1.

    Date 20/02/2008 16:50:50
    Log SQL Server (Current - 20/02/2008 16:56:00)

    Source Logon
    Message
    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: IP ADDRESS]
    It seems like about 600-700 over 4 minute period. This time, it seemed to resovle itself - earlier in the day (around 12:00), the cluster failed over.
    The only references I've seen so far suggestion a register key "hack"(http://www.sqlservermart.com/resources/SynAttackProtect.aspx):
    Resolution

    Add the following registry key and reboot the server.
    HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParametersSycAttackProtect{DWORD} = 0
    Anyone got anything they can add?
    Further to the registry hack, this concerning word of caution from the MS forums (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1308037&SiteID=1):
    Security Note:
    Setting this registry key may expose the server to a SYN flood, denial-of-service attack. Add this registry value only if necessary and with an understanding of the security risks. Remove this registry value when testing is complete.
  8. kzimmerm New Member

    I've put this hack in place... but the problem that I discovered is a memory leak and don't really know how this "denial of service" attack has anything to do with it.
    Right now I have a process that checks the memory usage of sqlservr.exe. I've been tracking memory prior to the last "blow-up". I was able to set a threshold to give me about 24hrs advance notice to simply restart the SS services.
    I've put the hack in place but have not restarted the server... I can't afford that much time down to restart the server.
    Kurt
    DBA
    RHWI, Inc
    Poughkeepsie, NY


  9. CEEQL New Member

    Recently we have experienced the same issue and when we checked we couldn't find any issues with cpu, userconnections, memory etc on the server. Atlast we looked into temp db drive and found that it was assigned to just one .mdf file.
    Then we created additional .ndf files based on number of processors(Remember sql server set up best practises?) and it did the trick.
    Cheers

Share This Page