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)





Last Modified: 12 December 2016

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - All temp tables are global
2011-02-20 - MSSQL - Unsigned int
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - Adding SHASum support
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-29 - MSSQL - TRIM Function