How to Batch Set the Error Log Location for Multiple SQL Server Agents

If you are given a task of changing the SQL Server agent’s Error log location for a single SQL Server instance, you could easily do that by using SQL Server management studio. However, if the same task must be completed for almost 1000 SQL instances, you might take a month to complete it. In this article, we will learn how to update SQL Server agent’s Error log path through TSQL.

For a single instance

SQL Server Agent's Error Log LocationTo set a new location for the SQL Server agent’s error log, using SQL server management studio connect to the database engine of the server and expand the SQL Server agent node. Now right click on the Error Logs folder and click configure. This will open a form and by using this form you can set the new location. However, this new location will not come into effect until you restart the SQL Server agent. You can restart SQL Server agent directly from the management studio or from the SQL Server configuration manager or use services.msc

Let’s automate it

T-SQLExecute this script from a central server. This script will set a new location for the SQL Server agent’s error log. In addition to that, with a 10-second delay, the script will restart the SQL Server agent. This is to give immediate effect to the change in location. Before executing this script, it is expected that you have created linked servers for all target servers. These linked servers should be mentioned in the script to get loaded in the table @Tserver.

DECLARE @Tserver TABLE 
  ( 
     cserver VARCHAR(200) 
  ) 

INSERT INTO @Tserver 
VALUES      ('SERVERNAME') 

DECLARE c1 CURSOR FOR 
  SELECT * 
  FROM   @Tserver 
DECLARE @cmd    NVARCHAR(2000), 
        @server VARCHAR(200) 

OPEN c1 

FETCH next FROM c1 INTO @server 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      SET @cmd = N'EXEC (''EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file = N''''C:\NEWErrorLog\SQLAGENT.OUT'''' EXEC xp_servicecontrol N''''STOP'''',N''''SQLServerAGENT'''' WAITFOR DELAY ''''00:00:10'''' EXEC xp_servicecontrol N''''START'''',N''''SQLServerAGENT'''' WAITFOR DELAY ''''00:00:10'''''') at [' 
                 + @server + ']' 

      PRINT @cmd 

      EXEC (@cmd) 

      FETCH next FROM c1 INTO @server 
  END 

CLOSE c1 

DEALLOCATE c1

Dynamic scripts are created and executed on each linked server. The new location is set using the sp_set_sqlagent_properties. You can manually verify the new location using sp_get_sqlagent_properties. This sp must be executed on the msdb database. If you execute on another database, you would see the following error message

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure ‘sp_get_sqlagent_properties’.

The location of the Error log file mentioned in the script is an example and it is not necessary that you should use the same path. You can modify it with a new location as per your requirement and it is expected that this path is present on all linked servers before executing the script.

Tweak it

Instead of using a single location, you can easily customize this script to set a new location for each linked server. Please note, a corrupt SQL Server would still allow you to see its startup parameters though configuration manager.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook recovery and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.