SQL Server hangs and becomes unresponsive - what can I do?

A. Not an easy question, but here's a checklist that you can run through. It assumes that NT itself responds - if it doesn't then SQL Server isn't the problem, and you should see bluescreen.txt in the faq for more information on this condition.

When it "locks up" can you connect at all to SQL Server? Try connecting locally on the server using ISQL.EXE - ISQL -Usa -P and see if it connects. This doesn't run any stored-procedures when it connects so any blocking going on (say in tempdb) won't affect it.

If you can connect what does a select * from sysprocesses and sp_lock show? Look for blocked spids or unusual wait-types.

If you can't connect to SQL Server are you able to make a network drive connection to the NT server itself? Are you able to ping it's IP address?

Are there any errors in the SQL errorlog?

Are there any errors in the NT system/application event logs?

Do you have SMP=-1 and/or priority boost=1 set in sp_configure? If you do then SQL queries can monopolize your system at the expense of NT/network drivers. This can cause spasmodic lock-ups. Set SMP and Priority Boost to 0 (the default).

What does task manager/performance monitor show SQL Server doing? Is it using 100% cpu?

If you can't get to the bottom of the problem then you need to call Microsoft PSS (see MSPSS.TXT in the FAQ for more info) and give them details of the problem and the results of the above tests.

In the mean-time if you need to stop/start SQL Server quickly then use the following (as normal service shutdown commands probably won't work - try them first anyway). 

You can kill SQL Server with "KILL sqlservr.exe". The KILL command is in the NT resource kit. It can then be started with NET START MSSQLSERVER. ONLY do this if SQL Server does not respond to a normal shutdown or NET STOP MSSQLSERVER command. This will at least stop you from having to reboot NT.


===

v1.02 2000.02.01
Applies to SQL Server versions : All
Related FAQ articles : n/a
Related Microsoft Kb articles : n/a
Other related information : n/a