PRB: SQLServerAgent Service May Stop Responding (hang) After You Restart the Computer (306655)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q306655

SYMPTOMS

Sometimes, after you restart a computer, the SQLServerAgent service may appear to stop responding (hang). If you run this command from a command prompt

NET START

you can see that the SQL Agent has not started, although it is set to automatic. If you open the Services applet in Control Panel and press the Startup button for the SQLServerAgent, this error message appears:
The service database is locked.
The error message also appears with other services if you click their Startup buttons.

CAUSE

The error message shown in the "Symptoms" may occur for several reasons one of which is if the SQLServerAgent service does not start after you restart the computer even though the service is set to automatic. If the error message occurs because the SQLServerAgent service is having difficulty starting, it is because the SQLServerAgent service is holding the service control manager's database open until a failure or success occurs. Under normal circumstances, if the SQLServerAgent service starts, the database closes and the error message does not appear.

RESOLUTION

If you manually press the SQLServerAgent Start button, the service starts properly. However, this action does not satisfy the necessity for an automatic startup of the SQLServerAgent service. After the SQLServerAgent service starts, you can press the Start button on any other service with no additional problems.

To work around the problem, configure the SQLServerAgent service to startup automatically. To configure the SQLServerAgent service to startup automatically:
  1. Set the SQLServerAgent service startup to Manual.
  2. Create a startup stored procedure that invokes NET START for the SQLServerAgent service. For instance:
    xp_cmdshell 'net start sqlserveragent'
With the preceding code, the SQLServerAgent service automatically starts every time SQL Server starts.

For example:

CREATE PROC StartSQLAgent 
 AS 
 EXEC master..xp_cmdshell 'net start sqlserveragent'
GO
EXEC sp_procoption 'StartSQLAgent', 'startup', 'on'
NOTE: Do not use this approach with a clustered SQL Server installation.

This approach does not deal with the underlying problem, it only provides a workaround.

STATUS

This behavior is by design.

MORE INFORMATION

To investigate this problem, examine the SQLAgent.out file for clues as to what process is encountering a problem. Typically, this behavior may occur because of a problem with the initialization of an extended stored procedure (xproc). For example purposes only, here is a demonstration of the problem as it relates to a specific mail profile with which SQL Mail and SQLAgent Mail is configured to use.

In this particular case, SQLAgent does not stop responding at startup if it is set to start under the LOCAL SYSTEM account. The SQLAgent also starts automatically with the use of a domain account, as long as SQL MAIL is not using the specific mail profile.

If you examine the SQLAgent.out file, it indicates that a clean startup is not occurring, and that the last event attempted was the authentication of the mail profile.

2/25/99 7:00:51 AM - ? [129] SQLServerAgent starting under Windows NT service control
2/25/99 7:00:56 AM - ? [100] Microsoft SQLServerAgent version 7.00.623 (x86 unicode retail build) : Process ID 195
2/25/99 7:00:56 AM - ? [101] SQL Server SQL version 7.00.623 (0 connection limit)
2/25/99 7:00:56 AM - ? [102] SQL Server ODBC driver version 3.70.623
2/25/99 7:00:56 AM - ? [103] NetLib being used by driver is DBNMPNTW.DLL; Local host server is (local)
2/25/99 7:00:56 AM - ? [310] 1 processor(s) and 256 MB RAM detected
2/25/99 7:00:56 AM - ? [339] Local computer is SQL running Windows NT 4.0 (1381) Service Pack 4
2/25/99 7:00:56 AM - ? [124] Subsystem 'TSQL' successfully loaded (maximum concurrency: 20)
2/25/99 7:00:56 AM - ? [124] Subsystem 'CmdExec' successfully loaded (maximum concurrency: 10)
2/25/99 7:00:57 AM - ? [124] Subsystem 'Snapshot' successfully loaded (maximum concurrency: 100)
2/25/99 7:00:57 AM - ? [124] Subsystem 'LogReader' successfully loaded (maximum concurrency: 25)
2/25/99 7:00:57 AM - ? [124] Subsystem 'Distribution' successfully loaded (maximum concurrency: 100)
2/25/99 7:00:57 AM - ? [124] Subsystem 'Merge' successfully loaded (maximum concurrency: 100)
2/25/99 7:00:57 AM - ? [124] Subsystem 'ActiveScripting' successfully loaded (maximum concurrency: 10)
2/25/99 7:00:57 AM - ? [392] Using MAPI32.DLL from C:\WINNT\SYSTEM32 (version 4.0.993.3)
2/25/99 7:00:57 AM - ? [196] Attempting to start mail session using profile 'TestMailProfile'...
You must investigate this particular problem from the standpoint of whether SQL Mail is properly configured as described in the following Microsoft Knowledge Base article:

263556 INF: How to Configure SQL Mail

However, if you verify that SQL Mail is configured properly, and the problem still occurs, then Microsoft recommends that you open a support case with Microsoft Product Support Services for further troubleshooting and analysis.
Modification Type:MajorLast Reviewed:7/1/2002
Keywords:kbprb KB306655
©2002 Microsoft Corporation. All rights reserved.