Check logspace on multiple servers

In SSMS setup all your servers in a group, see https://www.brentozar.com/archive/2008/08/sql-server-2008s-new-central-management-server/ for a splendid howto.

Now connect to them all by right-clicking on the group and choosing New Query, this new query window will have its status list at the bottom in a pinkish/red hue instead of the usual yellow to show that, yes we are connected to a group.

I often use dbcc sqlperf(logspace) to quickly get logspace usage for all databases on the instance, and we can do this for all servers at once. When I wrote this I was only interested in those servers and databases that had used more than 10% of its log, and no system databases. Came up with the following

CREATE TABLE #logspace
( [dbname] sysname
, logSizeMB float
, logSpaceUsedPct float
, Status int);

INSERT INTO #logspace
EXEC ('DBCC SQLPERF(LOGSPACE);')

SELECT dbname
, logSizeMB
, CONVERT(INT, logSpaceUsedPct)
, logSizeMB - (logSizeMB * logSpaceUsedPct / 100) AS LogSpaceUnusedMB
FROM #logspace
WHERE logSpaceUsedPct > 10
AND dbname not in ('master','msdb','model','tempdb','SSISDB')
ORDER BY logSpaceUsedPct DESC;   

DROP TABLE #logspace

As always, run this on a testserver first and when you have verified it to work, go for prod.

Have a good day!

Leave a Reply

Your email address will not be published. Required fields are marked *