Deploying a SQL Agent Job to cycle the Error logs to multiple servers

So, in the previous post we talked about changing the number of SQL Error logs. With that done, we now need to actually make use of all these logs. I like keeping each days errors in its own log file. Then if a problem arises and it was last week Tuesday, well then look in the corresponding log file.

There is a system stored procedure sp_cycle_errorlog, that will do exactly the above, close one log and open a new log. All I do now is schedule this stored procedure as a SQL Agent Job to run at midnight every day. Thats easy enough on 1 server, but remember, I have 151. So I script out the SQL Agent Job. Great, now I have a .sql file that I need to deploy to 151 servers. Thats if I'm not using a master - target server configuration. That in itself is a fantastic concept, but I've never seen it at any client I've started at.

Another blogger, SerialSeb, posted a very handy piece of code that does what I need to do. Check out his post.

Basically what he does is create a batch file that contains a sqlcmd command. This command as you know can execute the contents of a file against a said SQL server. Then the code runs the batch file using cmd /c. Go to the command prompt and type: cmd /?. It returns "/C Carries out the command specified by string and then terminates". If the batch file exists the contents of it are overwritten. Good one Seb.

Great, so unit of work encapsulted, got list of servers, its go time.

foreach ($server in $serverlist) {Execute-SqlFile $file $server $dbname $WindowsAuthentication=true}

Where $file is the .sql file containing the T-SQL for the SQL Agent job. $dbname doesn't matter because the .sql file changes the database to MSDB anyway.

Go forth and deploy.