SQLAgent doesn't restart after NT reboot 1

[SQLSquirrel]

I administer several identical NT servers all of which host SQLServer 7.00.996 (sp3 or sp4). Since the weekend, the SQLAgent refuses to restart on one of the servers and there is no apparent error message in the SQL logs or NT event log - although there is always a message confirming the SQLAgents has restarted!

MSSQLServer and SQLAgent are using the local system account on this server.

I am aware that the server was rebooted by an automated security patching process (along with numerous other servers) since it last worked, but the guys responsible for that are not going to know or admit to any known issues.

Does anyone know what the cause may be?

Does anyone know how to restart a SQLAgent from another server (I already monitor several SQLAgents from a single linked server - to give warning during office hours - but this is critical and I need to restart this failed SQLAgent during the overnight batch)?

 

[JayKusch]

OK first off ... you can make sure that SQL Server Agent starts by checking if the "Autostart SQL Server Agent" check box is indeed checked. You can find this by going into Enterprise Manager(EM) and right clicking on the server itself. Then choose Properties. On the General tab you should see the check box.

You can the drill further in EM to Management..SQL Server Agent. Once again right click and choose Properties. In the Advanced Tab you should see two other check boxes that control when and how SS Agent starts. On the General tab you will find what credentials are starting the service THIS MAY BE WHERE THE PROBLEM LIES!!!

Now ... I have a bit of nifty code that will create a system stored procedure once executed and implemented. Then, from that point on, SQL Server Agent will ALWAYS start when the SQL Server service is started even when a system reboot happens. Here is the code ...

USE Master
GO

CREATE PROC AutoStart_SQLAgent
AS
BEGIN
/***********************************************************
Just run this complete script in the master database. This script creates a stored procedure that autostarts SQL Server Agent, when SQL Server starts. This procedure will be marked as a startup procedure, so that it runs everytime SQL Server service starts. For this approach to work successfully, the 'scan for startup procs' option should be 
1. This option can be changed by using sp_configure system stored procedure. Notice the @ServiceName variable in the code, which holds the name of the SQL Server Agent service. In SQL Server 7.0 the SQL Server Agent service name will always be SQLServerAgent. In case of SQL Server 2000, the SQL Server Agent's service name for a default instance would be SQLAgent. If you have a named instance of SQL Server 2000, the SQL Server Agent service name will be SQLAgent$InstanceName, where InstanceName is the name of the SQL Server named instance. In the above two cases, you will have to set the appropriate value for @ServiceName variable. Upon successfully starting the SQL Server Agent, this procedure will log a message in the SQL Server error log saying "Successfully started SQL Server Agent".
***********************************************/

DECLARE @Err int, @Msg varchar(100), @ServiceName sysname

  SET @ServiceName = 'SQLServerAgent'

  EXEC master.dbo.xp_servicecontrol 'START', @ServiceName 

	SET @Err = @@ERROR

	IF @Err = 0
		BEGIN
		 RAISERROR ('Successfully started SQL Server Agent', 1, 1) WITH LOG
		END
		ELSE
		BEGIN
		 SET @Msg = 'Error occured while starting SQL Server Agent. Error code: ' + STR(@Err)
			RAISERROR (@Msg, 18, 1) WITH LOG
		END
END
GO

EXEC sp_procoption 'AutoStart_SQLAgent', 'startup', 'true'
GO

Thanks

J. Kusch

 

[SQLSquirrel]

All my check boxes are set correctly etc so I'm going to try the code - thanks very much for this.