Friday, August 7, 2020

Can you query SQL Server's Error Log file location or size?

Why yes.  You can!  Working a new server today that I have limited access to... and I needed a way to get around that limitation.  I just needed to find the location of the SQL Server error log files -- and you know what else?  The size, too, because it was taking an ungodly amount of time to open the error log within SSMS.  The server hasn't been cycled in months, so I was sure the thing was pretty big.  

What does the DBA without sysadmin privileges do?  😏 

 

DECLARE @logfiles TABLE (

       [FilArchive#] TINYINT,

       [Date] DATETIME,

       [LogFileSizeB] BIGINT

)

 

INSERT @logfiles

EXEC xp_enumerrorlogs

 

SELECT

       [FilArchive#],

       [Date],

       CONVERT(VARCHAR(50),CAST(SUM(CAST([LogFileSizeB] AS FLOAT)) / 1024 / 1024 AS DECIMAL(10,4))) + ' MB' SizeMB

FROM

       @logfiles

GROUP BY

       [FilArchive#],

       [Date],

       [LogFileSizeB];

 

-- to identify error log file location

SELECT SERVERPROPERTY('ErrorLogFileName') [Error_Log_Location];


The current one is not quite as large as I expected, but here you go -- the location and size of the current and all archived SQL Server Error logs.


Enjoy!


DBCC SQLPERF(logspace) -- Only one database, or with conditions.

Many SQL Server DBAs often use 'DBCC SQLPERF(logspace)' to analyze transaction log utilization for their databases.  It is very helpful.  When run exactly as I've quoted (but without the quotes), you will return log usage details for all databases on the server:


But - what if you have a ton of databases on the server and you don't want to be scrolling through everything to find what you care about?  Or, maybe you only wish to see those databases where the log usage is greater than X%. 

Here's a quick little trick to use DBCC SQLPERF(logspace) with conditions, only returning details for the databases you care about.  

-- optional parameters

DECLARE

       @dbname VARCHAR(155) = NULL,

       @SpaceUsed FLOAT = NULL

 

DECLARE @LOGSPACE TABLE(

       dbName VARCHAR(155),

       LogSizeMB FLOAT,

       [LogSpaceUsed%] FLOAT,

       [Status] INT

       )

INSERT @LOGSPACE

EXEC ('DBCC SQLPERF(''logspace'')')

 

-- Now pull it back for review

-- if your optional parms are null, you return log usage for all databases

SELECT dbName, LogSizeMB, [LogSpaceUsed%][Status]

FROM @LOGSPACE

WHERE (dbName = @dbName OR @dbName IS NULL)

AND ([LogSpaceUsed%] >= @SpaceUsed OR @SpaceUsed IS NULL);


Here I have populated my optional @SpaceUsed parameter, to only return details where the log is 30% used or greater.  Most likely you'll want to use a higher @SpaceUsed, but this is just a visual so you can see what I mean.

Or in this case, I've input the @dbName:                            


Hopefully you find it useful!


More details here:

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-sqlperf-transact-sql?view=sql-server-ver15