Forgotten Maintenance – Cycling the SQL Server Error Log

Most of us get caught up in fragmentation, finding the slowest queries, and looking at new features. We forget the little things that make managing a SQL Server easier – like cylcing the SQL Server error logs.

What’s the Error Log?

The SQL Server error log is a file that is full of messages generated by SQL Server. By default this tells you when log backups occurred, other informational events, and even contains pieces and parts of stack dumps. In short, it’s a treasure trove of information. When SQL Server is in trouble, it’s nice to have this available as a source of information during troubleshooting.

Unfortunately, if the SQL Server error log gets huge, it can take a long time to read the error log – it’s just a file, after all, and the GUI has to read that file into memory.

Keep the SQL Server Error Log Under Control

It’s possible to cycle the SQL Server error log. Cycling the error log starts a new file, and there are only two times when this happens.

  1. When SQL Server is restarted.
  2. When you execute sp_cycle_errorlog
Change everything!

Change everything!

When SQL Server cycles the error log, the current log file is closed and a new one is opened. By default, these files are in your SQL Server executables directory in the MSSQL\LOG folder. Admittedly, you don’t really need to know where these are unless you want to see how much room they take up.

SQL Server keeps up to 6 error log files around by default. You can easily change this. Open up your copy of SSMS and:

  1. Expand the “Management” folder.
  2. Right click on “SQL Server Logs”
  3. Select “Configure”
  4. Check the box “Limit the number of error log files before they are recycled”
  5. Pick some value to put in the “Maximum number of error log failes” box
  6. Click “OK”

It’s just that easy! Admittedly, you have to do this on every SQL Server that you have, so you might just want to click the “Script” button so you can push the script to multiple SQL Servers.

Automatically Rotating the SQL Server Error Log

You can set up SQL Server to automatically rotate your error logs. This is the easiest part of this blog post, apart from closing the window.

To cycle error logs on a regular basis, restart your SQL Server nightly.

Only joking.

You can set up a SQL Agent job with a T-SQL step. All it has to do is EXEC sp_cycle_errorlog. Schedule the SQL Agent job to run as frequently as you’d like and you’re good to go. The upside of this approach is that it’s automatic and the SQL Server error logs will be more granular, making it easier to find the error messages you’re looking for.

It’s Just That Easy!

Cycling the SQL Server error log is easy – you just need a regularly scheduled agent job. Rotating the logs makes it easier to find error messages. Let’s face it – you’re only looking for error messages when there’s a problem. That’s all there is to rotating the error logs.

Previous Post
Window Functions and Cruel Defaults
Next Post
SQL Server 2016 CTP2.4: Maintenance Plan Changes

38 Comments. Leave new

  • Haha, you got me with:

    To cycle error logs on a regular basis, restart your SQL Server nightly.

    I was like…WHAT??!!??

    Reply
  • So, to be clear, each time I cycle the logs with that sproc, it starts a new log, cycles the existing ones, and deletes the tail. Or, in other words, if I have the Sql Server default of 6 logs, and I “EXEC sp_cycle_errorlog” on a daily basis, I will have a max of 6 days worth of logs. Correct?

    I’m going to have to determine how this works in connection with my use of “sysmail_delete_log_sp”, “sp_purge_jobhistory”, and “sp_delete_backuphistory”. Namely, if everything ultimately ends up in the same log, this is going to mess me up. I keep 365 days of backup history, but only 60 days of email-logging and 14 days of job history (lots of noise from successful tlog backups). If I start cycling the logs on a daily basis, it seems I’d need to change my server limit to 365 logs at bare minimum. Am I understanding things correctly?

    Reply
    • Backup history is kept in MSDB. Job history is also kept in MSDB. As is your email history. So… Nope, you’re right to be concerned, but cycling the error log won’t ruin your history retention.

      The error logs can contain some of the information you’re interested in but it’s stored as unstructured data in a text file on disk. Those files can grow quite large if you don’t maintain them.

      On the bright side, there’s a wealth of information about system health in the SQL Server error log, and it’s helpful to have those files around to search through.

      Reply
  • BTW: Same goes for sp_cycle_agent_errorlog

    Cheers,
    Johan

    Reply
    • sp_cycle_agent_errorlog – we tried that for awhile, but there isn’t anywhere (that I can find) to set the number of files to keep (as you can with the sql error log). It always kept the last 10 files. Is this still the case, or am I missing something?

      Reply
      • That is correct, but nothing is preventing you to copy the most recently archived sqlagent errorlog file to a safe zone. ( and clean up after a desired number or time )


        [string]$TargetSQLInstance = '.\SQL2014DE' #$args[0];

        #clear-host
        # Target instances

        if ( $TargetSQLInstance -eq $null ) {
        Write-Error "No SQLInstance given !!!"
        break
        }

        if ( $TargetComputerName -eq $null -or $TargetComputerName -eq '.' ) {
        $TargetComputerName = $Env:COMPUTERNAME
        }

        WRITE-verbose "--> $TargetComputerName \\ $Env:COMPUTERNAME <--"
        if ( $TargetComputerName -ne $Env:COMPUTERNAME ) {
        WRITE-OUTPUT "Nog niet voorzien voor remoting !!!" -BackgroundColor Red -ForegroundColor Yellow
        break
        }

        $TargetSQLAgentErrorlogs= "$env:TEMP\SQLAgentErrorlogs"

        # Load the SMO assembly and create the server object, connecting to the server.
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

        # You cannot set the number of SQLAgent Errorlog files to keep before rollover, so copy to safe zone
        # Keep in mind these files may be big, so can consume quite some disk space.

        #Create target folder if it doesn't exist
        if ( !( Test-path $TargetSQLAgentErrorlogs -PathType Container )) {
        md $TargetSQLAgentErrorlogs
        }

        $CycleTs = Get-Date -Format 'yyyyMMdd_HHmmss' ;

        $SQLInstance = $TargetSQLInstance
        try {
        $server = New-Object 'Microsoft.SqlServer.Management.SMO.Server' ($SQLInstance)
        #Take controle over the actual connect
        $server.ConnectionContext.Connect()

        $JobServerErrorlogFile = $server.JobServer.ErrorLogFile ;

        #Cycle SQLAgent errorlog
        $server.JobServer.CycleErrorLog();

        $TargetFileName = Split-Path -Path ($JobServerErrorlogFile -replace '.out', ".$CycleTs") -Leaf ;
        #Now copy the latest SQLAgent errorlog history file
        Copy-Item -LiteralPath ($JobServerErrorlogFile -replace '.out', '.1') -Destination (Join-Path -Path $TargetSQLAgentErrorlogs -ChildPath $TargetFileName ) ;

        WRITE-OUTPUT $('[{0}] - SQLAgenterrorlog file copied to [{1}].' -f $server.Name, (Join-Path -Path $TargetSQLAgentErrorlogs -ChildPath $TargetFileName ) )

        $server.ConnectionContext.Disconnect() ;
        }
        catch {
        WRITE-OUTPUT $('Execption encountered for [{0}] : {1}' -f $SQLInstance, $_.exception.message ) #-ForegroundColor Black -BackgroundColor Red
        }

        Reply
  • We have a job exactly like Jeremiah describes, with one additional step. After we run sp_cycle_errorlog, we import the previous version into an archive table in our admin database. This facilitates historical searches, which can be especially helpful if any of your apps write customized status information to the error log via xp_logevent.

    — Archive table
    CREATE TABLE [dbo].[ErrorLogArchive](
    [ErrorLogArchiveID] [int] IDENTITY(1,1) NOT NULL,
    [LogDate] [datetime] NULL,
    [ProcessInfo] [varchar](50) NULL,
    [Text] [varchar](max) NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    — The procedure our SQLAgent job runs
    CREATE PROCEDURE ArchiveErrorLog
    AS

    EXECUTE sp_cycle_errorlog

    IF @@Error = 0
    BEGIN
    INSERT ErrorLogArchive(
    [LogDate],
    [ProcessInfo],
    [Text])
    EXECUTE sp_readerrorlog 1
    END

    Reply
  • I must be missing something here – what version of SQL Server does this apply to?

    Reply
    • Are you using SQL Server? It applies. Books Online goes back to SQL Server 2005 on this, so that’s as far as I’m willing to say it works.

      You can always check the fantastic documentation by doing a search for site:msdn.microsoft.com SQL Server sp_cycle_errorlog when you need to know where a certain piece of functionality applies.

      Reply
  • I use a SQL Agent job to automatically cycle the errorlog when it reaches a given size, and also sends an email. If there are very large log files or if it cycles too frequently, then there is probably something that needs attention.

    https://ronthepolymath.wordpress.com/2015/09/30/cycle-sql-error-log-when-it-reaches-a-certain-size/

    Reply
  • Alex Friedman
    October 1, 2015 1:39 am

    Yeah, daily cycling is very helpful. I set it up on all my instances, with the max possible retention of 99 files.
    BTW, while the GUI limits you to 99 files, if you script it out you can set a larger limit. Though I’m not sure you’d really want to.

    Reply
  • I also recycle the log daily (at midnight) and keep 30 logs. Before doing the recycle, my job first scans the current log for failed logins, and sends an html-format email to the DBA’s if the number of failures for any login is greater than a defined threshold. It’s proved useful for highlighting persistent login failures.

    Reply
  • Just be aware of the 99 files limit. If your goal is to keep logs for 90 days, some “unexpected” SQL Server restarts (SQL patching restart because of Windows patching, etc.), may prevent you from having all the logs (in terms of days) you want to keep.
    In these cases you either need to copy the old files to a safe place (as mentioned earlier here) or otherwise put the content safe.

    Reply
  • Neisl Grove-Rasmussen
    October 5, 2015 1:55 am

    Great post anbout a basic task that I think still is important.
    We have increased the number of errorlog files to 42 (what else) and are recycling on a daily basis at 23:59:30. Then the file is in the filesystem with last active (LastWriteTime) on the SQL Server active day.
    This way we have about one month SQL Server Errorlog history on the server including restarts.
    The parsing of the files before or after recycle is a great idea, but I still struggle to catch files generated by multiple restarts.

    Reply
    • I realize this is an old thread, but I too was concerned about dropping off logs due to unexpected restarts or patching. So I created this script, which, so long as you don’t have multiple restarts within a 24 hour period will prevent unnecessary recycling of the error log.
      DECLARE @ver AS nvarchar(256) = REPLACE(@@Version,CHAR(10),CHAR(13)+CHAR(10))
      DECLARE @tbl AS TABLE (LogDate DATETIME, ProcessInfo varchar(50), Text varchar(MAX))

      INSERT INTO @tbl
      EXEC xp_readerrorlog 0, 1, @ver

      IF ((SELECT MIN(LogDate) FROM @tbl) < GETDATE()-1)
      EXEC sp_cycle_errorlog

      Reply
  • Patrick ORegan
    May 24, 2016 1:52 pm

    I realize this is somewhat old, but what have you folks done to address a common error:

    [412] Errorlog has been reinitialized. See previous log for older entries.

    I have used the syntax:
    USE msdb
    GO
    EXEC dbo.sp_cycle_agent_errorlog
    GO

    I’ve ran this in both a query window and with an SQL Agent job. Each fail with the above error. Additionally, if I right click on the error log folder in SSMS, it again fails with this error. Only successful “recycle” is restarting the service in Configuration Manager.

    This is SQL 2014 at patch level 12.0.4449.0, it has two instances (one named, the other default), it has replication used to push my Ozar and Ola scripts to a DBADB on TSX instances from an MSX Multi-server Administration “management” server. They all fail….on the MSX and Targets (TSX).

    This appears to be a problem many others have had, but I’ve yet to find a solid resolution to the issue. Thanks for helping!

    Reply
  • probably worth cycling the agent logs as well.. sp_cycle_agent_errorlog

    Reply
  • […] knew I needed to recycle the Log Files and needed to quickly google the command and all credit goes HERE and the command I needed to issue was EXEC […]

    Reply
  • Sorry to poke at an old posting.

    I’m wondering why is cycling the log a best practice? Other than a smaller file for the server or client to load when accessing the log I don’t see any real advantage. Actually I prefer to be able to look at the list of logs and quickly and easily see when the instance and/or server was last restarted, which is much harder to do if you recycle the log daily or weekly as you need to read the dates and times closely to see which ones happened during the scheduled recycles.

    Reply
    • Kenney – sure, read the post, specifically paragraph #3.

      Reply
      • If I’m counting correctly then paragraph #3 is “Unfortunately, if the SQL Server error log gets huge, it can take a long time to read the error log – it’s just a file, after all, and the GUI has to read that file into memory.”

        So I still have the same question, other than a smaller file size what is the advantage? Because if it’s only a smaller file then I’ll prefer to not cycle the file so that I can easily see instance/server restarts at a glance.

        I just want to be sure of the Pros and Cons before I dismiss this practice or adopt it. So far I see the only Pro for cycling the log file is a smaller file (which means faster load time when viewing and less memory consumed while open). The only Pro I see for not cycling is being able to see instance/server restarts at a glance (looking at the list of log files in SSMS). The only Cons that I know of for cycling are that you can’t see restarts at a glance, and you have to increase the number of logs kept if you want to see more than 6 days/weeks/? of logs depending on your cycle interval. The only Cons that I know of for not cycling is that the file can be very large (which can take longer longer to load and use more resources), and you have a more variable amount of time that is covered by your logs (whether or not not you change the number of logs kept value).

        Reply
  • I had this set to cycle 99 files … just found that it has reset to 6. No idea when that happened, but I suspect the upgrade to SQL2017 might have overridden the setting. My “Archive #7” is a few days before we upgraded to SQL2017.

    Reply
  • Sorry to drag this one up again but I’ve just migrated from 200R2 to 2017 and now when my recycle job runs nightly it doesn’t attach a timestamp to the log when viewed in SSMS. So I get current, archive#1, archive#2 archive#3 etc, when what I want is current, archive#1 – 19-08-2019 : 01.00.00, and so on. Any ideas what I mightnot be doing?

    Reply
  • My SQL server went through some weird issue.
    It was disconnected until I changed the database from stand alone to online.
    When looking at error log, issued time period is completely missing.
    And also, error cycle had automatically ran, creating new errorlog file.
    Would anyone know how this could happen?

    Reply
  • Just a quick one. If I wanted to do this with a limited user, what permission would this user require? I’d only want to cycle the error logs. I’ve tried granting execute on sp_cycle_errorlog to this user but it doesn’t seem to work

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}