Unable to shrink T-logs of SQL server databases?

I had worked on the issues where I observed the increasing size of t-logs caused huge disk space usage. In this article I will discuss about how we can handle these situations. The whole intent of this blog is to help you troubleshoot the issue of shrinking the log files. Sometimes you might have observed that even after running dbcc shrinkfile multiple times, the ldf file size remained the same.

Here is how we start the troubleshooting of these kinds of issues:

1. We need to run the query and check what’s the log_reuse_wait for the transaction logs of  that database :

select LOG_reuse_wait_desc,name from sys.databases
where  name= <‘database_name’>

To check the kinds of waits we can see in the output , please see the article : – http://msdn.microsoft.com/en-us/library/ms178534.aspx

More often we see the issues due to 1. active transaction 2. Log backup 3. replication

In this article, I will discuss majorly about active transaction and Log backup. I will discuss very little about replication as it will need a different troubleshooting altogether.

2. Scenario 1 , when we see the log_reuse_wait as log_backup then first thing we need to do is to backup the transaction logs.

Step 1 – select LOG_reuse_wait_desc,name from sys.databases where  name= <‘database_name’>

you will see log_backup under log_reuse_wait_desc column.

This method will mark all the inactive VLFs ready for truncation. In the full recovery model the t-logs can’t be truncated unless a log backup is done. Same log backup is used to do point in time recovery.

Step 2 – Backup log <‘database_name’> to disk = ‘Path\filename’

Step 3 – DBCC shrinkfile ( logfile_filename,<size>).

If the log file is still not shrunk, please run the above steps in same sequence.

Please note : – After the logs are shrunk (using the below option) we need to take the full backup or differential backups of the database. It breaks the log chain and don’t run it on production server.

If t-log backup is not feasible then there is another easier way to resolve the same issue. If it’s time critical then instead of taking the log backup , you could change the recovery model to simple. It will make the current LSN’s reusable as per it’s properties and will allow the shrink.

After changing the recovery model if you run the same query again:

Step 1: select LOG_reuse_wait_desc,name from sys.databases where  name= <‘database_name’>

you will see log_backup under log_reuse_wait_desc column.

Step 2: Change the database recovery model to simple and run select LOG_reuse_wait_desc,name from sys.databases where  name= <‘database_name’>

you will see the output of log_reuse_wait_desc to “nothing”

Once  the above is done, we are ready for shrink. Here is the command which could be used to shrink

Step 3: DBCC shrinkfile ( filename,<size>).

For more information on the above command please check the article: – http://msdn.microsoft.com/en-us/library/ms189493.aspx

Scenario 2 : –  when the Log_reuse_wait_desc is active transaction then no matter whats the recovery model, you will see the tlogs growing.

To check the open transactions we need to run : DBCC opentran .

From it’s output you will get the SPID which has open transaction.

To find what that SPID is doind, you could run select * from sys.dm_exec_requests where session_id=<SPID> ;

check if it’s waiting on some resource and try to rectify the slowness to finish the transaction.

Once the transaction is completed then you could run the same query : – select LOG_reuse_wait_desc,name from sys.databases where  name= <‘database_name’> to check the further waits and so on

Scenario 3 : –  When the Log_reuse_wait_desc is replication, this can happen we have transactional replication is enabled.

The logs can’t be cleared/reused unless the t-logs are sent to the subscriber. Normally, whenever there is huge delay in sending the t-logs to the subsrciber – we may end up in this situation.

For this scenario, you will have to troubleshoot replication and find out why the sync is taking such a long time.

Please note: – unless you are completely sure that replication is not required please don’t follow the below mentioned steps:

I have even seen some scenarios where the customers had deleted the replication (may be – not completely) and then also they saw replication as log_reuse_wait_desc.  In those situations, best is to run sp_removedbreplication(http://msdn.microsoft.com/en-us/library/ms188734.aspx) for that database to remove the replication completely.