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
To 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
Execute 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.
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.
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