Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Find SQL Server databases where log file is too large


By:   |   Read Comments (2)   |   Related Tips: 1 | 2 | More > Database Administration

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

I support thousands of databases and I'm running across situations where I have a database that has a data file of a couple hundred megabytes and the associated log file is gigabytes in size! I understand this is because I may not have my transaction log backups scheduled properly. What I'm interested in is an easy way to identify these situations where the SQL log file is too large, so I can go in and further analyze these problem databases without needing to look at each database.

Solution

Log file growth (and some would say bloating) is an issue many SQL Server DBAs face on a daily basis. When it comes down to it, the issue of log file growth is a direct outcome of configuring your database to run in a logged mode (FULL or Bulk-Logged) and either ignoring the need to - or forgetting to configure and schedule transaction log backups for the database. In most cases however this can occur if the activity being logged is at such a volume that your log backup schedule may not be as frequent as it should be or you may have encountered situations where some issue prevented backups to run as scheduled for a period of time.

I've created the following script that looks at the maximum log backup size in the master database tables that retain your backup metadata. It then compares that max size for each database to the size of the transaction log file for each database on the instance and then returns those databases where the historical maximum log backup file size is less than the current size of the transaction log.

Note: before running the following code you need to create the sp_foreachdb stored procedure from this previous tip written by Aaron Bertrand.

--+-----------------------------------------------
--Create necessary temp table for results
--+-----------------------------------------------
IF EXISTS 
 (
 SELECT name 
 FROM tempdb.sys.[tables] 
 WHERE name LIKE '#trx_log_size%'
 )
 DROP TABLE #trx_log_size;
CREATE TABLE #trx_log_size
 (
 database_name nvarchar(128) NOT NULL,
 [name] nvarchar(128) NOT NULL,
 physical_name nvarchar(260) NOT NULL,
 size_mb int NOT NULL
 )
 
--+-----------------------------------------------
--Populate temp table with current log file sizes 
--+-----------------------------------------------
EXEC [dbo].[sp_foreachdb] 
 @command = 'INSERT INTO [#trx_log_size] ([database_name], [name], 
                    [physical_name], [size_mb]) 
                    SELECT N''?'', name, physical_name, size*8/1024 
                    FROM [?].sys.database_files WHERE type = 1;',
  @suppress_quotename = 1
--+-----------------------------------------------
/*
File size::backup size (Full recovery DBs)
 ctl+shift+m to replace days param to include in
  analysis of backup history
*/
--+-----------------------------------------------
SELECT
 L.[database_name], 
 L.[physical_name], 
 L.[size_mb], 
 MAX(CEILING(BF.[backup_size]/1024/1024)) AS max_backup_file_size_mb,
 L.[size_mb] - MAX(CEILING(BF.[backup_size]/1024/1024)) AS file_excess_mb
FROM msdb.dbo.[backupfile] BF 
 INNER JOIN msdb.dbo.[backupset] BS ON [BF].[backup_set_id] = [BS].[backup_set_id]
 INNER JOIN [#trx_log_size] L ON [BS].[database_name] = L.[database_name]
 INNER JOIN master.sys.[databases] SD ON L.[database_name] = SD.[name]
WHERE BS.[type] = 'L'
-- AND BS.[backup_start_date] > DATEADD(d,,GETDATE())
 AND SD.[recovery_model_desc] = 'FULL'
GROUP BY SD.[name], L.[database_name], L.[physical_name], L.[size_mb]
HAVING  L.[size_mb] > MAX(CEILING(BF.[backup_size]/1024/1024))
ORDER BY L.[size_mb] - MAX(CEILING(BF.[backup_size]/1024/1024)) DESC;
--+-----------------------------------------------
--Clean up your messes when you're done!
--+-----------------------------------------------
DROP TABLE #trx_log_size;

After creating the sp_foreachdb SP and running the above code in my environment I can see the databases that have issues as shown below.

To make this easier to analyze all results returned are converted to megabytes. The output is as follows:

  • database_name - name of the database
  • physical_name - physical file name for the transaction log file
  • size_mb - current size of the transaction log file
  • max_backup_file_size_info - maximum backup file size for a transaction log backup
  • file_excess_mb - excess space that is being used by transaction log

maximumnlog backup size in the master database tables

Based on the output I can see I have 5 databases that have a potential issue. So, once you have the list of databases to review you can then proceed with analyzing each one individually to determine if you wish to reduce the size of the log file, alter the backup schedule, or some combination there-of.

Next Steps
  • Review, download and run Aaron Bertrand's improved sp_msforeachdb code for this tip as well as other queries you need to run across all databases.
  • Run the above code in your environment to see if you have any potential transaction log size issues.
  • Note that the amount of history to take into consideration when compiling maximum backup size is supplied via a templated parameter. To use this parameter you need to uncomment the line and simply use the keystroke combination of Ctl+Shift+M and supply the parameter value that signifies the number of days you want to take into consideration for historical backup information. To learn more about templates read this tip.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, August 18, 2014 - 6:25:22 AM - Oleg Back To Top

to avoid calling sp_foreachdb  Code:

--+-----------------------------------------------
--Populate temp table with current log file sizes 
--+-----------------------------------------------
EXEC [dbo].[sp_foreachdb] 
 @command = 'INSERT INTO [#trx_log_size] ([database_name], [name], 
                    [physical_name], [size_mb]) 
                    SELECT N''?'', name, physical_name, size*8/1024 
                    FROM [?].sys.database_files WHERE type = 1;',
  @suppress_quotename = 1

can be replaced with :


--+-----------------------------------------------
--Populate temp table with current log file sizes
--+-----------------------------------------------
INSERT INTO [#trx_log_size] ([database_name], [name],
[physical_name], [size_mb])
SELECT DB_NAME([database_id])AS [DatabaseName], name, physical_name, size*8/1024
FROM sys.master_files WITH (NOLOCK)
WHERE type_desc = 'LOG'
AND [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2

 


Monday, May 09, 2011 - 11:15:00 AM - Michael K Campbell Back To Top

Tim, 


Great approach/script - I like the logic involved in how you're determining whether a log file is too large. It's an ingenious approach.

Curious though why all the extra 'hassle' of using an sp_foreachdb approach when master..master_files contains all of the same info? 

Am I missing something about how that info is queried/updated? Seems to me you could do the same thing without all that 'foreachdb' stuff as follows: 

WITH LogFileSizes (database_name, name, physical_name, size_mb)

AS (

SELECT

DB_NAME(database_id),

name,

physical_name,

CONVERT( bigint, size/128.0)

FROM 

master.sys.master_files

WHERE

type = 1

)

 

SELECT

L.[database_name], 

L.[physical_name], 

L.[size_mb], 

MAX(CEILING(BF.[backup_size]/1024/1024)) AS max_backup_file_size_mb,

L.[size_mb] - MAX(CEILING(BF.[backup_size]/1024/1024)) AS file_excess_mb

FROM msdb.dbo.[backupfile] BF 

INNER JOIN msdb.dbo.[backupset] BS ON [BF].[backup_set_id] = [BS].[backup_set_id]

INNER JOIN LogFileSizes L ON [BS].[database_name] = L.[database_name]

INNER JOIN master.sys.[databases] SD ON L.[database_name] = SD.[name]

WHERE BS.[type] = 'L'

-- AND BS.[backup_start_date] > DATEADD(d,<days_in_backup_sample,int,-1>,GETDATE())

AND SD.[recovery_model_desc] = 'FULL'

GROUP BY SD.[name], L.[database_name], L.[physical_name], L.[size_mb]

HAVING  L.[size_mb] > MAX(CEILING(BF.[backup_size]/1024/1024))

ORDER BY L.[size_mb] - MAX(CEILING(BF.[backup_size]/1024/1024)) DESC;

Which begs the question, what am I missing? 

 

 


Learn more about SQL Server tools