Code For Food: SQL Server - Troubleshooting transaction log

In this article, I will write about some common use case when dealing with transaction log in SQL Server.

Hopes it helps! :D

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

 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:
https://msdn.microsoft.com/en-us/library/ms179478.aspx

5. Shrink transaction log size

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:

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!

Code For Food

Monday, September 5, 2016

SQL Server - Troubleshooting transaction log