THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Setting SQL Server ErrorLog Retention and Rollover with Powershell

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"

trap [System.Exception]
    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.

Published Wednesday, March 3, 2010 6:00 AM by Jonathan Kehayias



Ronald Dameron said:

Nice post! Didn't know you could use trap in such a global fashion.

March 3, 2010 6:32 AM

Vidhya Sagar said:

I'm doing the exact thing with T-SQL.. This is good.

Can you refer me some good articles or books to study Powershell?

March 3, 2010 12:18 PM

Jonathan Kehayias said:


I just downloaded PowerGui and started writing.  It provides intellisense for Powershell, and has a nice IDE that makes writing scripts easy.  I did a bit of googling along the way to find specifics but I never really saved any links or hit anything that would stand out as a good overall tutorial.  There are some articles on SimpleTalk specific to SQL Server and Powershell usage, but if you can write .NET code, you can easily handle powershell.

The best reference would probably be the Windows Powershell Script Center on Technet:

March 3, 2010 3:22 PM

The Rambling DBA: Jonathan Kehayias said:

This month’s TSQL Tuesday event is being hosted by Pat Wright (Blog | Twitter) and  the topic this

February 7, 2011 11:19 PM
Anonymous comments are disabled

This Blog


Privacy Statement