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:
No comments:
Post a Comment