Is Your SQL Server Database Transaction Log Too Big?
It is not common to have SQL log files that are larger than the data files. If so, this could indicate a possible problem:
Possibly a poor maintenance plan (Backups are not being performed correctly).
There is a process with an oversized transaction. Or the transaction is never committed.
SQL replication may break.
How to find databases with Log files larger than Databases files?
You can use the following code. It returns all databases with log files (over 1gb) larger than the data file.
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)
Read the details below for information on how to do it:
- Check that you are using the correct recovery model.
- Verify maintenance plans.
- Minimize the transaction log file.
