Is your SQL Server database transaction log too Large?

It is not common to have files larger than data files.

Usually, transaction logs have 10-60% of the size of the data files.

This may indicate some issues:

  • A possible bad maintenance plan (Backups are not being performed correctly).
  • Log-based replication can be broken (Database mirroring, replication, or AlwaysOn Availability Groups);
  • Someone begins a transaction using bad practices and much more.

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 files.

  2. DB_NAME(a.database_id) AS DatabaseName,
  3. CAST((CAST(a.size AS BIGINT) * 8 / 1000000) AS NVARCHAR(20)) AS 'LogFileSize_GB'
  4. FROM sys.master_files a
  5. WHERE a.type = 1
  6. AND DB_NAME(a.database_id) >4
  7. AND a.size > 125000 /* Size is measured in pages here, so this gets us log files over 1GB. */
  8. AND a.size > ( SELECT SUM(CAST(b.size AS BIGINT))
  9. FROM sys.master_files b
  10. WHERE a.database_id = b.database_id
  11. AND b.type = 0)
  12. AND a.database_id IN (
  13. SELECT database_id
  14. FROM sys.databases
  15. 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

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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