Tag: transaction log

SQL Server – Log file too big and disk is full, now what?

SQL Server – Log file too big and disk is full, now what?

Have you ever faced the problem when your transaction log grows too big and your disk is full?

You cannot shrink the transaction log file unless you do transaction log backup first. You cannot do the transaction log backup because your disk doesn’t have enough free space. The transaction log is getting bigger every minute. The clock is ticking. What will you do?

Normally, SQL Server would make you backup the transaction log first, only then would allow you to shrink transaction log file. If you want to look more into this process, take a look at this blog.

However in this case, there’s not enough space to write the log backup file. So that wouldn’t work.

After spending a lot of time googling, I ended up this solution


where DBNAME should be changed to the database name that we are dealing with.

What this piece of code does is that it backups the transaction log to ‘NUL’ file, which means the backup process will be executed but there’s no writing to disk. After this process completes, SQL Server will think that all the transaction log has been backed up to disk and allow us to shrink the transaction log file.

This solution is perfect, except for one thing: during this process, all transaction log will be thrown away, which means if we can’t make a full backup of the data after this process, we might face a data loss if the server crash.

Therefore, use this solution at your own risk. And remember to do a full backup of the database right after this process, just to be sure. After the full backup of the database, you’re safe.

Hope this helps!


Troubleshooting Transaction Log in SQL Server

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

   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
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
    (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



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:


Bonus: view size of each table in database

   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
   sys.tables t
   sys.indexes i ON t.OBJECT_ID = i.object_id
   sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
   sys.allocation_units a ON p.partition_id = a.container_id
   t.NAME NOT LIKE 'dt%'
   AND t.is_ms_shipped = 0
   AND i.OBJECT_ID > 255
   t.Name, p.Rows


Thanks for reading.

Happy coding! 😀