question

arun.zakaria avatar image
arun.zakaria asked

Not able to login to SQL Server

I am getting the following error on my SQL Server 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 During this time, the users cannot log in to the database and when i try to open SQL Managment Studio I am not able to login to that instance. Once i restart the server,it seems to be normal. The SQL server is SQL Server 2005 running on Windows Server 2003 64 bit Standard edition. This is a VM with 4vCPUs and 12288 MB RAM. Any suggestions? Thanks **[edited to include error messages]** Event Type: Information Event Source: MSSQL$WEB Event Category: (2) Event ID: 9724 Date: 04.08.2013 Time: 06:33:55 User: N/A Computer: SQLSRV Description: The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'The service queue "ExternalMailQueue" is currently disabled.' For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: fc 25 00 00 0a 00 00 00 ü%...... 0008: 10 00 00 00 53 00 51 00 ....S.Q. 0010: 4c 00 53 00 52 00 56 00 L.S.R.V. 0018: 30 00 31 00 5c 00 42 00 0.1.\.B. 0020: 43 00 43 00 57 00 45 00 C.C.W.E. 0028: 42 00 00 00 05 00 00 00 B....... 0030: 6d 00 73 00 64 00 62 00 m.s.d.b. 0038: 00 00 .. and the error log Event Type: Error Event Source: MSSQLSERVER Event Category: (4) Event ID: 17189 Date: 04.08.2013 Time: 06:34:00 User: N/A Computer: SQLSRV Description: 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:] For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 25 43 00 00 10 00 00 00 %C...... 0008: 09 00 00 00 53 00 51 00 ....S.Q. 0010: 4c 00 53 00 52 00 56 00 L.S.R.V. 0018: 30 00 31 00 00 00 00 00 0.1..... 0020: 00 00 .. I noticed this happening when I started the full backup of SQL server from Backup Exec. Once i restarted the server and stopped this process from running it started working.
sql-server-2005error-message32-bitwindows-server-2003
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
Can you please confirm the service pack level of your SQL Server and what Edition of 2005 you have installed please? Are there any details in the SQL Server Error log or the Windows Event Log as the error text suggests?
0 Likes 0 ·
arun.zakaria avatar image arun.zakaria commented ·
The service pack level on SQL server is service pack4. The following is the info of SQL server. Microsoft SQL Server Management Studio - 9.00.5000.00 Microsoft Analysis Services Client Tools-2005.090.5000.00 Microsoft Data Access Components (MDAC)- 2000.086.3959.00 (srv03_sp2_rtm.070216-1710) Microsoft MSXML- 2.6 3.0 6.0 Microsoft Internet Explorer- 8.0.6001.18702 Microsoft .NET Framework- 2.0.50727.3649 Operating System- 5.2.3790 2.0.50727.3649 Operating System 5.2.3790 Would this info be sufficient? Thanks
0 Likes 0 ·

1 Answer

·
Tim avatar image
Tim answered
Check the error logs for messages starting with the text "A significant part of sql server process memory has been paged out...". If you find that, then the 'failed to spawn a thread' message is misleading you: your problem is related to Windows paging SQL Server out to disk, and not the number of worker threads available. You may want to consider setting min memory as well as max memory and consider turning on lock pages in memory since you are on 64 bit.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

arun.zakaria avatar image arun.zakaria commented ·
Hi Tim, Thanks for the update. Unfortuanely I didnt see any error logs starting with the text "A significant part of sql server process memory has been paged out...". The only logs are information log **[edit]** moved the error messages into the question **[/edit]** I noticed this happening when I started the full backup of SQL server from Backup Exec. Once i restarted the server and stopped this process from running it started working. This server hosts over 100 databases. So does this have any thing to do with the number of worker threads available? Thanks
0 Likes 0 ·
Tim avatar image Tim commented ·
Based on your comments below it is thread issue. With it happening when you launch Backup Ex. You are experiencing worker thread starvation in the instance. Look at the THREADPOOL wait type in sys.dm_os_wait_stats and it should have a high number of waiting tasks and duration. You may want to look to see if you can limit the number of CPU's that backup ex is using. I know Red Gate SQL Backup Pro allows you to specify the number of CPU's.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.