Is your SQL Server database transaction log too large?

It is not common to have SQL log files larger than data files. If it is, this may indicate a potential problem:

How to find databases with Log files larger than database files?

You can use the script below. It will return all databases with logs files (over 1GB) larger than data file.
DB_NAME(a.database_id) AS DatabaseName,
CAST((CAST(a.size AS BIGINT) * 8 / 1000000) AS NVARCHAR(20)) AS 'LogFileSize_GB'
FROM sys.master_files a
WHERE a.type = 1
AND DB_NAME(a.database_id) >4
AND a.size > 125000 /* Size is measured in pages here, so this gets us log files over 1GB. */
AND a.size > ( SELECT SUM(CAST(b.size AS BIGINT))
FROM sys.master_files b
WHERE a.database_id = b.database_id
AND b.type = 0)
AND a.database_id IN (
SELECT database_id
FROM sys.databases
WHERE source_database_id IS NULL)

How can I fix the issue?

Read the details below for information on how to:
  1. Check if you are using the correct recovery model.
  2. Verify the maintenance plans.
  3. Shrink the transaction log file.

More information

Microsoft – A transaction log grows unexpectedly or becomes full in SQL Server
Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

One Response

  1. Hey Jose,

    thanks for this script (even though a little buggy…). It helped point out some issues around recovery models and types of backups that were set up and what changes needed to be made.


Leave a Reply

Your email address will not be published. Required fields are marked *