As a consultant, I have to be able to quickly spot problems, and one of the problems I frequently find is transaction log files that are incorrectly sized.
There are two catalog views in the master database which make this easy to do – sys.master_files and sys.databases. The sys.master_files view contains the database and individual file names, and the data_space_id column always has a value of 0 for the log file. The size column returns the value in 8KB pages, so we have to multiply the column by 8, then divide by 1024 to get the size in megabytes (MB).
I’ve constructed the query to sum up all the non-log pages because I want to see the overall database size, and I also want to see the logical name of the log file, along with its size. This is most easily done using a table expression for each part of the query, then I join them together on database name
SELECT d.[Database],
d.[Database Total],
l.[Log File Name],
l.[Log Total]
FROM
(
SELECT d.name AS 'Database',
SUM(m.size * 8 / 1024) AS 'Database Total'
FROM sys.master_files m
INNER JOIN sys.databases d
ON d.database_id = m.database_id
WHERE m.data_space_id <> 0
GROUP BY d.name
) d
INNER JOIN
(
SELECT d.name AS 'Database',
m.name AS 'Log File Name',
m.size * 8 / 1024 AS 'Log Total'
FROM sys.master_files m
INNER JOIN sys.databases d
ON d.database_id = m.database_id
WHERE m.data_space_id = 0
) l
ON l.[Database] = d.[Database]
ORDER BY d.[Database];
This query then produces the results shown below.
This query lets me review at a glance which databases might need attention with respect to setting transaction log size, and gives me the logical name of the log file so I can use that in DBCC SHRINKFILE(log_file, size) as needed.
1 thought on “Quickly Query Database and Log Size in T-SQL”
Comments are closed.