SQL Server Tips

SQL Server Log File Too Big

Updated October 29, 20252 min read

SQL Server log file too big? How to fix it. Ploom stares up at a giant ledger towering over a tiny book.

Log files are larger than data files

It is not common to have SQL Server 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 log files (over 1GB) larger than the data file.

T-SQL
SELECT 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?

To address the issue, follow the instructions provided below:

  1. Check if you are using the correct recovery model.
  2. Verify the maintenance plans.
  3. Shrink the transaction log file.

References:

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜