I have recently been dealing with a situation with a client where the transaction log has become an issue. Naturally during the research portion of trying to figure out what is wrong I wanted to find things like the size of the transaction log or what the autogrowth is set to. You can find out much of this information through the UI relatively easily. However, it becomes a pain when you are checking on multiple databases to have to keep looking up the same information. Luckily everything you could possibly want is stored in system tables. I had a good base for what I wanted to see but ran into a bit of an issue with the FILEPROPERTY command. No big deal, after a little searching I found the reason why it wasn’t working, you have to be in the database where the files are. i.e. you have to be in the AdventureWorks database to run SELECT FILEPROPERTY(name, ‘SpaceUsed’ FROM sys.master_files
While doing some random looking around I found a blog that had a better way of accomplishing my goal than I was about to start coding, so I kinda took that idea and used some of it and modified it a bit to add the fields I wanted to see and use table variables instead of temp tables. So, thank you to Nagaraj Venkatesan for giving me some ideas. I don’t want to take credit for all of this code, so here is a link to the blog I got it from. Here is the code that I have with my modifications made to it. Hope this makes someone’s life a little easier, I sure know it is making my day tomorrow easier!
/*Create the necessary variables*/
DECLARE @DatabaseIDs TABLE (DatabaseNumber INT IDENTITY(1,1), DatabaseID INT, DatabaseName VARCHAR(250))
DECLARE @DatabaseInfo TABLE (DatabaseName VARCHAR(250), LogicalName VARCHAR(250),FileType VARCHAR(20), PhysicalName VARCHAR(500), [Size(MB)] DECIMAL(38,2), [Used(MB)] DECIMAL(38,2), [Used(%)] DECIMAL(38,2), [Available(MB)] DECIMAL(38,2), [Available(%)] DECIMAL(38,2), MaxSizeInMB VARCHAR(20), GrowthRate VARCHAR(50))
DECLARE @DatabaseCount INT
DECLARE @DatabaseNumber INT = 1
DECLARE @DatabaseName VARCHAR(250)
DECLARE @SQLText VARCHAR(4000)
/*Populate the list of Database IDs and Database Names*/
INSERT INTO @DatabaseIDs (DatabaseID, DatabaseName)
SELECT dbid AS DatabaseID, name
FROM MASTER.dbo.sysdatabases
/*Get a count of how many databases there are*/
SELECT @DatabaseCount = COUNT(*) FROM @DatabaseIDs
/*Loop over each database and insert the requested informaiton into the table*/
WHILE @DatabaseNumber <= @DatabaseCount
BEGIN
SELECT @DatabaseName = DatabaseName
FROM @DatabaseIDs
WHERE DatabaseNumber = @DatabaseNumber
SET @SQLText = ‘
USE [‘ + @DatabaseName + ‘]
SELECT
DB_NAME(database_id) AS DatabaseName,
Name AS LogicalName,
CASE WHEN type_desc = ”ROWS” THEN ”Data File” WHEN type_desc = ”LOG” THEN ”Log File” ELSE ”Unknown” END AS FileType,
Physical_Name AS PhysicalName,
size/128.0 AS FileSizeInMB,
CAST(FILEPROPERTY(name, ”SpaceUsed”) AS DECIMAL(38,6))/128.0 AS UsedSpaceInMB,
((CAST(FILEPROPERTY(name, ”SpaceUsed”) AS DECIMAL(38,6)))/(size)) * 100 AS PercentUsed,
size/128.0 – CAST(FILEPROPERTY(name, ”SpaceUsed”) AS DECIMAL(38,6))/128.0 AS AvailableSpaceInMB,
((size – CAST(FILEPROPERTY(name, ”SpaceUsed”) AS DECIMAL(38,6)))/(size)) * 100 AS PercentAvailable,
CASE WHEN max_size = -1 THEN ”Unrestricted” ELSE CONVERT(VARCHAR, max_size) END AS MaxSizeInMB,
CASE WHEN is_percent_growth = 1 THEN CONVERT(VARCHAR, growth) + ” %” ELSE CONVERT(VARCHAR, growth/128) + ” MB” END AS GrowthRate
FROM sys.master_files
WHERE type_desc IN (”ROWS”,”LOG”) AND DB_NAME(database_id) = ‘ + ”” + @DatabaseName + ””
INSERT INTO @DatabaseInfo
EXEC (@SQLText)
SET @DatabaseNumber = @DatabaseNumber + 1
END
/*Return the data for all tables*/
SELECT * FROM @DatabaseInfo ORDER BY DatabaseName
Here is a sample of the output:
