January 12, 2016 at 12:25 pm
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
January 12, 2016 at 2:49 pm
Seems this is a know issue. Here is the connect item on it:
January 12, 2016 at 4:05 pm
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".
January 13, 2016 at 10:26 am
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