Post reply

"current" errorlog size

  • January 12, 2016 at 12:25 pm

    #399555

    I am wanting to know the current errorlog file size.

    EXEC XP_enumerrorlogs

    That stored procedure only gives you the old cycled errorlog sizes. The current errorlog size is always zero until the file is closed in an errorlog cycle.

    Archive # Date Log File Size (Byte)

    0 01/11/2016 12:52 0

    1 01/11/2016 12:50 85504

    2 01/10/2016 23:45 448918

    3 01/07/2016 15:40 441444

    4 01/03/2016 23:24 377226

    5 12/31/2015 11:55 111588

    6 12/29/2015 16:55 206408

  • January 12, 2016 at 2:49 pm

    #1851434

    Seems this is a know issue. Here is the connect item on it:

    https://connect.microsoft.com/SQLServer/feedback/details/761308/xp-enumerrorlogs-returns-incorrect-results-when-running-on-windows-server-2008-r2

  • January 12, 2016 at 4:05 pm

    #1851443

    SELECT CAST(size / 128.0 AS decimal(9, 2)) AS size_mb

    FROM sys.database_files

    WHERE type = 1

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • January 13, 2016 at 10:26 am

    #1851661

    That MS workaround really works. I created a sproc to do this, I also submitted it as a script.

    Thank you for the replies.

    -- =============================================

    -- Author: Alan Speckman

    -- Create date: 20160113

    -- Description: Returns the size of the current SQL Server ERRORLOG file.

    --

    -- Example Call:

    -- DECLARE @errorlog_sizeMB INT

    -- EXEC usp_ERRORLOG_SIZE @size = @errorlog_sizeMB OUTPUT;

    -- SELECT @errorlog_sizeMB

    -- =============================================

    CREATE procedure usp_ERRORLOG_SIZE @size int OUTPUT

    as

    --create temp table to store output of xp_enumerrorlogs.

    CREATE table #enumerrorlogs (archive_number int, archive_date datetime, file_size_bytes int)

    --in order to get an accurate reading from the OS on the current size of the ERRORLOG file,

    --testing has proved that xp_readerrorlog will cause the file to be opened then close, so the OS

    --can return the current size of the ERRORLOG file. This does not recycle the errorlog, thus

    --creating a new errorlog file.

    EXEC xp_readerrorlog 0, 1, 'server', 'process ID'

    --populate temp table

    INSERT INTO #enumerrorlogs

    EXEC XP_enumerrorlogs

    --select only the current ERROLOG file size.

    select @size = file_size_bytes/1024 from #enumerrorlogs

    WHERE archive_number = 0

    --cleanup

    DROP table #enumerrorlogs

    GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply