By: Jeffrey Yao | 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:
- Find the SQL Error log location
- Find the current SQL Error log size and if it is bigger than a threshold, go to step 3 and cycle, otherwise quit
- 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.
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
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)
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;
- Learn some basic management tips from here SQL Server 2005 Error Log Management
- Learn other ways to Identify location of the SQL Server Error Log file
About the author
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