Cycle SQL Server Error Logs based on size using PowerShell

By:   |   Updated: 2014-05-13   |   Comments (7)   |   Related: > Error Logs


Problem

The SQL Server error log is notorious for growing really large when certain things happen; like repeated login failures, stack dumps or other errors. In cases when trace flag 3226 is not turned on and there are hundreds of databases being backed up the error log can also grow really big.  When the log gets big, it makes it difficult to find real issues, much slower and it can fill up your disk drive. Is there a way that I can manage the error log size efficiently?

Solution

The regular and blind approach is to schedule a job to run sp_cycle_errorlog periodically, I consider this approach over-administration. The ideal approach should be to recycle when needed, such as when the error log size is bigger than a specified threshold. In this tip, I will demonstrate how to use PowerShell to manage the error log size.

There are three key steps in this technical solution:

  1.  Find the SQL Error log location
  2.  Find the current SQL Error log size and if it is bigger than a threshold, go to step 3 and cycle, otherwise quit
  3.  Run sp_cycle_errorlog against the SQL instance.

I have seen in many SQL Server environments that Trace Flag 3226 is not turned on, and the potential issue is you will have many successful backup log records created in the SQL Server Error Log and this can pollute your error log and make it difficult to find needed information.  So in this PowerShell script, I include a parameter to allow you to turn on Trace Flag 3226 on the target SQL Server instance.

<#.SYNOPSIS
Manage SQL Error Log Size.
.DESCRIPTION
The Optimize-SQLErrorlog function will display the error log size of the given sq. instances,
and will recycle the sq. server instances' current error log based on an input threshold
It can also turn on TF 3226 to suppress backup successful info in the error log

.INPUTS
$ServerInstance: single or multiple sq. server instances where we will check and optimize the sq. error log size
$Threshold: integer number for MB, default 10
$DisableBackupInfo: switch, if present, will turn TF 3226 on if TF3226 not there
$Optimize: switch, if present, will recycle error log when current log size bigger than $Threshold MB

.OUTPUTS
Current SQL error log size info

.EXAMPLE
Optimize-SQLErrorLog -ServerInstance "jy\sql2k8", "jy\sql2012" 

This command is to check the sq. error log size on two sq. instances jy\sql2k8 and jy\sql2012

.EXAMPLE
Optimize-SQLErrorLog -ServerInstance "jy\sql2k8", "jy\sql2012" -threshold 1 -optimize
This command will 

#>
#You may need to run the following line to ensure that PowerShell can use the SQL Server SMO object
#assuming you have sq. server 2012 or sq. server 2008 (or R2) installed    
try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop;}
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"; }

Function Optimize-SQLErrorLog {
[CmdletBinding()] 

Param (
    [Parameter(Mandatory=$false, ValueFromPipeline=$true)] [string[]]$ServerInstance=$env:COMPUTERNAME,
    [ValidateRange(1, 999999)] [int]$Threshold=10, #MB, default to 10 MB
    [switch] $DisableBackupInfo,
    [switch] $Optimize
)

foreach ($svr in $ServerInstance) {
    try {
        $s = new-object Microsoft.SqlServer.Management.SMO.Server($svr);

        if ($s.version -eq $null) #means $svr may be a typo
        { $len = -1.00; }
        else {
            [string]$logpath = $s.ErrorLogPath;
      
            # if $svr is a named instance, we need to get the machinename
            [string]$mach = ($svr.split('\'))[0];
            if ($mach -ne $env:COMPUTERNAME)
            {
                $logpath = $logpath.Replace(':', '$');
                $logpath = "\\$mach\$logpath\ErrorLog";
            }
            else
            {
                $logpath = "$logpath\ErrorLog";
            }
                
            $itm = get-item $logpath;
        
            [decimal]$len = $itm.Length/1mb;
            if ($Optimize) {
                if ($len -ge $Threshold)
                {
                    ($s.Databases['master']).ExecuteNonQuery('exec sp_cycle_errorlog');
                    Write-Verbose -Message "sp_cycle_errorlog: $svr";
                }
            } #$Optimize
        
            if ($DisableBackupInfo) {
                ($s.Databases['master']).ExecuteNonQuery('dbcc traceon(3226,-1)');
                Write-Verbose -Message "Turn on TF3226: $svr";
            }# $DisableBackupInfo
        }#else;
        $SelectSplat = @{Property=('ServerInstance','LogSizeMB') };
        new-object -TypeName PSObject -Property @{
        ServerInstance = $svr
        LogSizeMB = "{0:N2}" -f ($len) } |
        Select-Object @SelectSplat;
        
    }#try
    catch
    {
        write-error -message $error[0].Exception.Message;
    }#catch
  } #foreach ($svr in $ServerInstance)
}#Optimize-SQLErrorLog

List 1 - Source Code

Example

We will start PowerShell as Administrator and run the following code

Optimize-SQLErrorLog -server "jeff-pc", "jeff-pc\sql2k8" -Optimize -threshold 5 -DisableBackupInfo;

List 2 - Recycle current error log if it is bigger than 5MB and turn on Trace Flag 3226

This is to cycle the error log on two SQL Server instances "jeff-pc" and "jeff-pc\sql2k8" if the current error log size is bigger than 5 MB.  Also we will turn on Trace Flag 3226 on the target instances.  We will get the following result, the LogSizeMB column shows the current error log size before the error log cycle is run.

OptimizeLog.JPG

To prove the current error log size is indeed recycled, we can run the following code

Optimize-SQLErrorLog -server "jeff-pc", "jeff-pc\sql2k8";

List 3 - Check current error log size

We will see the current log size is 0MB now

after_optimize.jpg

Next Steps
  • If you already have a customized PowerShell module, you can just copy and paste my code from List 1 to your existing PowerShell module.  You will just be adding a new function.
  • If you do not have a customized PowerShell module, you can save the List 1 as "dbatools.psm1" and then in create a folder like "DBATools" in the following folder tree (c:\windows\system32\WindowsPowerShell\v1.0\Modules\DBATools)

dbatools.JPG

After you start PowerShell (assuming PowerShell V2+), you just need to run: Import-Module DBATools to load the module and you can then run the command in List 2 and List 3 in the PowerShell ISE or a Command window.

  • Or You can create a SQL Server Agent Job with one step to run the two lines of code as follows and schedule the job to run daily.
    (The code below will recycle sql_server_1 and sql_server_2 error log if the current log size is bigger than 5MB.)
import-module dbatools;
Optimize-SQLErrorLog -server "sql_server_1", "sql_server_2" -threshold 5 -Optimize;


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2014-05-13

Comments For This Article




Saturday, August 9, 2014 - 1:16:32 AM - jeff_yao Back To Top (34071)

Hi Ron, thanks for trying out this tip.

I guess it is my bad that I did not make it very clear for sql server 2005/2008/2008R2. So try this way:

Save the two lines

import-module dbatools;
Optimize-SQLErrorLog -server "sql_server_1", "sql_server_2" -threshold 5 -Optimize;

as a PS script, let's call it c:\temp\a.ps1

In sql agent job step, choose type "Operating system (CmdExec)" and put in the following line in the step body

powershell.exe -file "c:\temp\a.ps1"

If you still have problems, please update here, and I will touch base you to figure it out.

Kind regards,

Jeff_yao

 

 

 

 

 


Friday, August 8, 2014 - 2:34:23 PM - Ron Back To Top (34068)

Hi Jeff, you are absolutely right! I saved the script and dumped it under my server's Windows\System32\WindowsPowerShell\v1.0\Modules\DBATools, next, and as your article suggests, I tried running it within my SQL Server Agent, and I get this error:

A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module dbatools;'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'import-module' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

Any ideas?


Wednesday, July 30, 2014 - 12:42:30 PM - jeff_yao Back To Top (33936)

@Ron, the registry key method applies to sql server 2012 and later. But for sql server 2005 and sql server 2008/R2, it wont work. Actually, I first deployed this method when I managed some sql server 2008 instances in 2010. :-)

 


Tuesday, July 29, 2014 - 8:25:14 PM - Ron Back To Top (33927)

Why not use the registry hack to limit the size of error logs? Nothing against this great article and the way it puts forward, but if there's a way to do it easily, I wonder why not, unless you guys think it's not a good idea:

http://support.microsoft.com/kb/2199578

USE [master];
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'ErrorLogSizeInKb', REG_DWORD, 5120;
GO


Wednesday, May 14, 2014 - 5:31:31 PM - Jeremy Kadlec Back To Top (30781)

Jeffery,

Congrats on your first tip!

Thank you,
Jeremy Kadlec
MSSQLTips.com Community Co-Leader


Tuesday, May 13, 2014 - 1:21:13 PM - jeff_yao Back To Top (30758)

@Louis, thanks for your comment.

Not sure what type of startup message you need to get from the log, are you thinking about the information like when the sql server is started or some specific information in the start-up log, like whether LockPageInMemory is enabled etc? If so, I'd recommend you to create an auto-start procedure that can automatically capture the data you need (using xp_readerrorlog for example) and store them into a permanent table.


Tuesday, May 13, 2014 - 1:01:47 PM - Louis Back To Top (30757)

This is a great tip, nothing like waiting forever for the log to load, and then have to scroll down a thousand lines or more to find out what happened last night. But there is one more thing that would be useful. If the server stays up for a long time (i.e. many months) and the error log is cycled regularly, the startup messages will eventually roll off. Even there are enough logs kept around, it will take a lot of time to figure out which is the first log from the last recycle. Does anybody have a script to capture the startup messages and keep them in a safe place?















get free sql tips
agree to terms