In few servers, we have hundreds of databases and sometimes it is tiresome and time-consuming activity to find the output of a DBCC command such as DBCC SQLPERF(Logspace) for a particular database.

For this kind of scenario , we can store the output of DBCC commands in a temp table and then can quickly find the desired results using SORT or WHERE .

In this example, I am going to share the script that I use for storing the result DBCC SQLPERF command in a temp table:

IF OBJECT_ID(‘tempdb..#temp’, ‘U’) IS NOT NULL
DROP TABLE #temp
GO
CREATE TABLE #temp (
DatabaseName VARCHAR(200)
,LogSize INT
,LogSpaceUsed INT
,[status] INT
)

INSERT INTO #temp (
DatabaseName
,LogSize
,LogSpaceUsed
,[status]
)
EXEC (‘dbcc sqlperf(logspace);’)

SELECT *
FROM #temp
ORDER BY LogSpaceUsed DESC

Hope this helps.

Cheers,

Subhro Saha

Advertisements