Troubleshooting Transaction Log in SQL Server

Dealing with SQL Server’s transaction log can be very troublesome.
In this post, we are going to take a look at some common use cases when dealing with transaction log in SQL Server.

Hopes it helps! 😀

1. View database size and log size

SELECT
   DB_NAME(db.database_id) DatabaseName,
   (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
   (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
   (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
   (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
 FROM sys.databases db
   LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
   LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
   LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
   LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id

or

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db

 

2. View % of log file that really contains data

DBCC SQLPERF(LOGSPACE);
GO

 

3. View the reason that log file cannot be reused

select name, log_reuse_wait_desc from sys.databases

 

4. Backup transaction log

Take a look at this article from Microsoft: Back Up a Transaction Log (SQL Server)

 

5. Shrink transaction log size

Do the following steps:

• In SQL Server Management Studio, right click database name then click on Task -> Shrink -> Files.
• Shrink file dialog will appear.
• In this dialog, make sure you select the following options:
• File type: Log
• Shrink action: Release unused space
• Then click OK.
• After the shrink action completes, recheck the database files’ size by executing scripts in step 1 – View database size and log size.

You can get more infomation in the following links:

• http://geekswithblogs.net/nagendraprasad/archive/2009/08/21/reduce-sizeshrink-transaction-log-of-the-db—sql-server.aspx
• http://sqlmag.com/blog/sizing-your-transaction-log
• http://www.sqlskills.com/blogs/paul/importance-of-proper-transaction-log-size-management/
• http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
• http://www.mssqltips.com/sqlservertip/1318/automating-transaction-log-backups-for-all-sql-server-databases/
• http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

 

Bonus: view size of each table in database

SELECT
   t.NAME AS TableName,
   p.rows AS RowCounts,
   SUM(a.total_pages) * 8 AS TotalSpaceKB,
   SUM(a.used_pages) * 8 AS UsedSpaceKB,
   (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
   sys.tables t
INNER JOIN
   sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
   sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
   sys.allocation_units a ON p.partition_id = a.container_id
WHERE
   t.NAME NOT LIKE 'dt%'
   AND t.is_ms_shipped = 0
   AND i.OBJECT_ID > 255
GROUP BY
   t.Name, p.Rows
ORDER BY
   t.Name

 

Thanks for reading.

Happy coding! 😀