In my last blog post I mentioned that I have been writing a lot of Powershell to simplify auditing and maintaining all of the servers I manage at my job. One of the items that I do on all of my servers is setup the SQL Server ErrorLog to rollover nightly so that if I need to look at the log for a problem, I am not scanning all of the log data since the server last restarted.
By default SQL Server only changes the ErrorLog when the service starts and for servers that run for months with FULL Recovery and fifteen minute log backups, unless Trace Flag 3226 is added to the server, a lot of log entries are generated for successful backups. By rolling the log over nightly, each log is one days entries and allows for rapid loading and scanning of the logs for entries of interest if a problem occurs. The easiest way to roll the log over is to create a SQL Agent job that calls sp_cycle_errorlog every night.
If you roll the logs over nightly, one thing you need to do is configure SQL Server to retain the ErrorLogs in case you need to look back over time. By default SQL Server only maintains the last six ErrorLogs. Unfortunately this value is still stored in a registry key, and the standard way to update the value is to right click on the SQL Server ErrorLogs folder in SSMS and select Configure to adjust the value. If you script the change out, you will find that it is calling xp_instance_regwrite to set the registry value:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30
This can be added in a script to allow deployment in a standardized fashion across all servers, but an easier way to do this is through Powershell and SMO. In SMO, the Microsoft.SqlServer.Management.Smo.Server object represents a SQL Server and it has a method set_NumberOfLogFiles() that can be used to change the number of log files maintained by SQL Server. In addition to this method, each Server has a JobServer object that represents the SQL Server Agent for the Instance. This can be used to create, schedule, and edit jobs on the SQL Server in a programmatic fashion.
A complete PowerShell solution to setting the log retention and creating a SQL Agent job to roll the logs over nightly would be:
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$Instance = "SQLServer"
write-host ("In Instance Loop trapped " + $_.Exception.GetType().Name + " For $Instance");
$smosvr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $_
if ($smosvr.Edition -notmatch "Express" -and $smosvr.Edition -notmatch "Desktop")
$agent = $smosvr.JobServer;
if ($agent.Jobs.Contains("Mtn-SQL ErrorLog Rollover"))
$agent.Jobs["Mtn-SQL ErrorLog Rollover"].Drop();
$job = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.Job') $agent, "Mtn-SQL ErrorLog Rollover"
$job.OwnerLoginName = 'sa'
$jobstep = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') $job, "Mtn-SQL ErrorLog Rollover"
$jobstep.SubSystem = [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::TransactSql
$jobstep.Command = "EXEC sp_cycle_errorlog"
$StartTS = new-object System.Timespan(0, 1, 0)
$jobSchedule = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobSchedule') ($job, 'Mtn-SQL ErrorLog Rollover')
$jobSchedule.FrequencyTypes = "Daily"
$jobSchedule.FrequencySubDayTypes = "Once"
$jobSchedule.ActiveStartTimeOfDay = $StartTS
$jobSchedule.FrequencyInterval = 1
$jobSchedule.ActiveStartDate = get-date
The first thing that this script does is load the SMO core assembly from the GAC into Powershell for use. Then it sets up a simple error handler to trap any errors that occur during the execution of the script, and creates a Server object for the SQL Instance specified. It then validates that the SQL Instance isn’t Express or Desktop Edition, which don’t have SQL Agent, and changes the log retention, and creates a Agent job to rollover the logs nightly.