Cleanup Pesky SQL Error Logs

If you are like me, you inherited variously configured SQL Servers when you took over as the DBA for your company. After almost two years, I have gotten all the standards in place where I feel that the environment is clean. One of the last things I accomplished was to standardize SQL Server Error Log configurations, Error Log Cycle schedules and cleaning up of old Error Logs.

To accomplish this there were several steps involved to get all SQL Servers into a unified set of configurations.

Steps To Take:

1. Set all SQL Server to have a log count equal to 30
2. Configure all SQL Server Agent maximum history rows to the highest value of 999999
3. Modify the SQL Server Agent job that cycles the Error Logs to a weekly schedule
4. Cleanup all SQL Server Error Logs older than 01/01/2020 from the Servers

1. Set all SQL Servers to have a log count equal to 30

SQL Server has configuration settings which control how many times SQL Server will rollover the error logs. By default, out of the box that number is 6. That means that no matter how many times you restart the SQL Server Service or execute

sp_cycle_errorlog you will only be able to store a maximum of 6 error log files.

In my environment, there were server configuration values from the default of 6 to the max of 99 and numerous iterations in between. In my mind, 30 sounded like a good number of error logs to keep around for historical purposes. There is more on how I came to that number on step 3 about cycling the error log and the schedule in which you perform that action.

$servers = Get-DbaRegisteredServer -SqlInstance "localhostsql2017"
Set-DbaErrorLogConfig -SqlInstance $servers -LogCount 30

Similar to most of the scripts I execute, I am going to load a variable called

$servers with the list of servers I plan to execute the PowerShell code against.

Now I am going to set the Log files count for all my servers to a value of 30.

2. Configure all SQL Server Agent maximum history rows to the highest value of 999999

Next up is to configure the maximum history row values on all my servers so I don’t lose any information that might be helpful for troubleshooting in the future.

By default SQL Server Agent will only keep 1000 of the most recent job history and 100 rows per job execution. I have seen where these defaults are obliterated by high frequency job execution systems. For example, you have a job that runs every minute. With the default of 1000 job history you will only be able to see a little over 16 hours of execution history for that job. It will be much less if you have a job that runs more often than every minute.

I have found that it does not have any negative impact if you bump these two settings to their maximum value of 999999.

Set-DbaAgentServer -SqlInstance $servers -MaximumHistoryRows 999999 -MaximumJobHistoryRows 999999

Using the same

$servers list you can run this command to set the configuration values for the SQL Server Agent History settings.

3. Modify the SQL Server Agent job that cycles the Error Logs to a weekly schedule

Let’s modify a lot of SQL Agent Job schedules!!!

Set-DbaAgentSchedule -SqlInstance $servers -Job "!Admin - sp_cycle_errorlog" -ScheduleName "CycleErrorLog - Daily - 12am" -NewName "CycleErrorLog - Weekly - 12am" -FrequencyType Weekly -FrequencyInterval 1 -StartTime 0 -FrequencyRecurrenceFactor 1 -Force

In this step we are going to change the schedule associated to our Cycle Error Log job with one command. We are changing the name of the schedule  from Daily to Weekly to match our new schedule. Also during this change we are modifying the interval of execution from Daily @ 12:00 AM to Weekly @ 12:00 AM.

As mentioned in Step 1, I was going to discuss more about the schedule of your error log cycle standards. When I arrived at my current gig and started looking around I found that the previous DBA was cycling the error log every day. I was not a fan of this configuration for several reasons. Some servers do not have much error log traffic, so this was a little overkill. You max out at 99 days of logs, and if your default is still 6 you only have 6 days worth of logs to review. With the ability to filter logs and query the logs and limit the output, having one log per day was not optimal. So that is now fixed and going forward we will document this as our best practice.

4. Cleanup all SQL Server Error Logs older than 01/01/2020 from the Servers

Time for some cleanup of the Pesky SQL Error Log files that were generated by doing a daily cycle of the error log. I am not going to be able to use a dbatools command for this cleanup. I put together a simple PowerShell script that will go out and remove all the error logs from before 01/01/2020. This is a date I felt comfortable basing my file cleanup on. Actually dbatools did make an appearance in this scrip. I am using

Get-DbaInstanceProperty to grab the ERRORLOGPATH for each of my SQL Servers.

foreach ( $s in $servername ) {
    $settings = Get-DbaInstanceProperty -SqlInstance $s.ServerName
    $path = $settings | Where-Object name -EQ 'ErrorLogPath' | Select-Object value
    $newpath = Join-Path "\$($s.ServerName)" $path.value.replace(':', '$')
    $filestodelete = Get-ChildItem -Path $newpath -Filter "ErrorLog.*" | Where-Object LastWriteTime -le '01/01/2020'
    Write-Host "Server Name: $($s.ServerName)"
    Write-Host "File Count: $($filestodelete.count)"
    foreach ( $f in $filestodelete ) {
        Remove-Item -Path $f.FullName -Confirm:$false
    }
}

That’s a wrap, everything is now clean and standardized!!!

If you found this blog handy, please use the Subscribe section at the top of the screen to join my newly created mailing list.  I hope to start delivering content via the mailing list soon.