"current" errorlog size

  • 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 #DateLog File Size (Byte)

    001/11/2016 12:520

    101/11/2016 12:5085504

    201/10/2016 23:45448918

    301/07/2016 15:40441444

    401/03/2016 23:24377226

    512/31/2015 11:55111588

    612/29/2015 16:55206408

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

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

    CREATEprocedure 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