"current" errorlog size

• alanspeckman

  Hall of Fame

  Points: 3344

  More actions

  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 #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

• JeremyE

  SSCoach

  Points: 16264

  More actions

  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

• ScottPletcher

  SSC Guru

  Points: 101180

  More actions

  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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• alanspeckman

  Hall of Fame

  Points: 3344

  More actions

  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

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

  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