After almost running out of space on a server. I decided to reclaim some space from some of the larger transaction log files from some of the active databases. The problem with log files is they can always grow but they never really shrink unless you take action to actually make the files smaller again. So here is a stored procedure to list all the databases and their log sizes sorted with the largest first of course.

However this can be a pointless exercise if done incorrectly. It will only work if you have been doing some sort of work on a database (eg a migration) and thelog files have grown much larger than their normal working size. So example of this would be to delete and import a new set of data. If you delete the data all in a single delete statements for a 10GB table then you will most likely have a log file of around 10GB or more. This will happen regardless even if you have the recovery model set to simple. After all during the transaction if you break a foreign key constraint or something goes wrong the database has to be able to roll back the transaction back to where it started before the delete statements. It will store this information in the log.

CREATE PROCEDURE LogSize
AS
        SELECT DB_NAME(database_id) AS DBName,
            Name AS LogicalName,
            Physical_Name, (size * 8) / 1024 SizeMB
      FROM sys.master_files
            WHERE type_Desc = 'LOG'
      ORDER BY SizeMB DESC
GO

As a quick explanation of the above. We read the sys.master_files which contains a list of all the files the database engine is using. We then narrow the search to only log files which is one per database. We then take the size (in pages)
and multiply it by 8 Kb. Then divide the answer again by 1024 to get it to a little more human friendly format in MB. After all we are not interested in trying to save a tiny amount of space.

Once we run the stored procedure using ..

EXEC LogSize

We will have a table of results

DBName LogicalName Physical_Name SizeMB

NetFlow NetFlow_log G:\MSSQL\Logs\NetFlow_log.ldf 513

We can then shrink the log files using the logical name and the dbcc command.

DBCC SHRINKFILE(NetFlow_log)