sqlskills-logo-2015-white.png

Limiting error log file size in SQL Server 2012

It’s quite well known that you can optimize error log file management using SSMS to change the maximum number of error log files to 99 and running sp_cycle_errorlog every day at midnight (see this post on my old SQL Server Magazine blog for graphics). This works in all current versions of SQL Server.

One thing that hasn’t been possible before is setting the maximum size of individual SQL Server error logs before SQL Server triggers cycling to a new error log. Well it turns out that in SQL Server 2012 you can!

While in my post-con workshop at SQL Intersection in Las Vegas last week, Jan Kåre Lokna (a former Immersion Event attendee from Norway) discussed some code he’s been experimenting with and I just heard from him that he got it to work.

The following code will set the maximum size of each error log file to 5MB on a SQL Server 2012 instance:

USE [master];
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'ErrorLogSizeInKb', REG_DWORD, 5120;
GO

I’ve tested this on SQL Server 2008 R2 and it does not work there, so the registry key must be new for SQL Server 2012. This is really useful to protect against gigantic error log files caused by repeated crash dumps, for instance when messing around with DBCC WRITEPAGE :-)

[Edit: 10/23/15] Also, for SQL Server 2014 the registry keys have changed again:

USE [master];
GO
-- Limit size of each file
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer',
N'ErrorLogSizeInKb', REG_DWORD, 1024;
GO

-- Number of ErrorLog Files
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer',
N'NumErrorLogs', REG_DWORD, 8;
GO

Note: I’ve had anecdotal reports that on SQL Server 2014, the SQL Server 2012 method is the only one that works. Please experiment and see which works in your situation. I imagine the difference may be due to fresh-install vs. upgraded-in-place.

You can read a more in-depth description on Jan’s blog here.

13 thoughts on “Limiting error log file size in SQL Server 2012”

  1. One problem with the “sp_cycle_errorlog every day at midnight” approach is that the logged initialization of SQL Server will (eventually) be lost.

    The following script, which relies upon xp_cmdshell (there are alternatives), preserves most of SQL Server’s logged initialization, without collecting subsequent errorlog bloat:

    USE [master]
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    CREATE PROCEDURE [dbo].[sp_archive_errorlog_initialization]
    AS
    DECLARE @PathNoExt varchar(400) = ‘C:\”Program Files\Microsoft SQL Server”\MSSQL11.MSSQLSERVER\MSSQL\Log\errorlog.’ — Edit for your -e path
    DECLARE @PathInit varchar(400) = @PathNoExt + CONVERT(char(8), GETDATE(), 112) + ‘_intitialization’
    DECLARE @CopyAppendCmd varchar(400) = ‘IF EXIST ‘ + @PathInit + ‘ COPY ‘ + @PathInit + ‘+’ + @PathNoExt + ‘ ‘ + @PathInit
    DECLARE @CopyCmd varchar(400) = ‘IF NOT EXIST ‘ + @PathInit + ‘ COPY ‘ + @PathNoExt + ‘ ‘ + @PathInit

    EXEC xp_cmdshell @CopyAppendCmd, no_output — xp_cmdshell needs to be enabled
    EXEC xp_cmdshell @CopyCmd, no_output
    GO
    EXEC sp_procoption N'[dbo].[sp_archive_errorlog_initialization]’, ‘startup’, ‘1’

    Reply

  2. Hi,

    This is my sql server version :

    > select @@version :

    Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (X64)
    Oct 19 2012 13:38:57
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

    I TRIED THE QUERY :
    USE [master];
    GO

    EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’,
    N’SoftwareMicrosoftMSSQLServerMSSQLServer’,
    N’ErrorLogSizeInKb’, REG_DWORD, 5120;
    GO

    IT IS NOT WORKING

    Reply

  3. Hi Paul,
    I believe this has moved to:
    USE [master];
    GO

    — Limit size of each file
    EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’,
    N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer’,
    N’ErrorLogSizeInKb’, REG_DWORD, 1024;
    GO

    — Number of ErrorLog Files
    EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’,
    N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer’,
    N’NumErrorLogs’, REG_DWORD, 8;
    GO

    Reply

  5. Strange. This will work inside SSMS but it doesn’t alter the registry.

    EXEC xp_instance_regwrite
    N’HKEY_LOCAL_MACHINE’,
    N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer’,
    N’NumErrorLogs’,
    REG_DWORD,
    5

    DECLARE @num_logs int;

    EXEC xp_instance_regread N’HKEY_LOCAL_MACHINE’,
    N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer’,
    N’NumErrorLogs’,
    @num_logs OUTPUT;

    SELECT number_error_logs=ISNULL(@num_logs,6);

    Reply

  6. Hello Paul,

    I am getting error

    “Msg 22022, Level 16, State 1, Line 0
    SQLServerAgent Error: The process cannot access the file because it is being
    used by another process.”

    while executing “sp_cycle_agent_errorlog”. Is there any way to fix this issue without restarting SQL agent. As We have more than 3K servers and this job fails on more than 50 servers weekly. I will be very thankful if you help me on this.

    Thank You!
    Vimal

    Reply

    1. It’s likely replication – merge or transactional. Could also be anti-virus software. If it’s replication, there’s not much you can do about it.

      Reply

  7. With my configuration of SQL Server 2014 it works only if I am using

    « EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\Microsoft SQL Server\MSSQLServer’, N’ErrorLogSizeInKb’, REG_DWORD, 5120; »

    if instead I am using this

    « EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer’, N’ErrorLogSizeInKb’, REG_DWORD, 1024; »

    a new sub key named « MSSQL12.MSSQLSERVER » with inside it a sub key named « MSSQLServer » inside the already existing « MSSQL12.MSSQLSERVER » key.

    So I end up with the key created in « HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL12.MSSQLSERVER\MSSQLServer ».

    Is it possible that xp_instance is automatically resolving « Software\Microsoft\Microsoft SQL Server\MSSQLServer » to this ?

    « SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer »

    a new sub key named « MSSQL12.MSSQLSERVER » is created …

    Reply

  9. One thing I ran into when trying to read the registry – needed to have double backslash before key which had spaces (in this case Microsoft SQL Server). Not sure if this is needed to write to the registry also. Does anyone know?

    EXEC xp_instance_regread
    N’HKEY_LOCAL_MACHINE’,
    N’SOFTWARE\Microsoft\\Microsoft SQL Server\MSSQL12.LAS1PRSQL001IN3\MSSQLServer’
    N’NumErrorLogs’; — or (ErrorLogSizeInKb)

    Reply

Leave a Reply

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

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course