Is there anyway I can control my log file size? It's growing like sky.

From Aug 1st 2015 I change log file size growth to 128/unlimited from 64MB/2097152 and now it's growing like hell for me. Excuse for my language because I tried to Shrink it's not work at all. I'm frustrated what to do on this?  This is my prod database.

Is there any suggestion? how to control my log file? or shrink any way?

• Moved by Kalman TothEditor 15 hours 54 minutes ago Not design

What is recovery model of database, how often you take transaction log backup ?

What is output of below(run it for DB whoes log file is growing)

select log_reuse_wait_desc from sys.databases where name='db_name'

Shrink is not working because some transactions (VLF's) might be active. You can query the log_reuse_wait_desc column in sysdatabases  to see why log cannot be shrinked. Possible causes

1. Active Transaction
2. Log Backup

Yes you can control the growth of your log file growth using auto growth settings and your backup strategy.

If you think your database transactions are frequent and huge volumes happen too quickly schedule your transaction log backups based on that frequency. say for eg: Every half an hour / every one hour. Again it depends on your SLA of recovery and downtime your stakeholders accept. It can be as frequent as 15 mts also incase of banking  :)

If you set the auto growth of Log file unrestricted, it will grow for sure but that will cause Disk space issues which in turn create problems for other files residing on that drive. So it is always good to keep the file restricted based on your LUN.

You said you changed from 128MB to 64MB why ???-- If your transactions are frequent you should make it to 256MB(recommended for log) or 500MB. Always remember to keep the setting in MB's rather than '%'. When you specify a lower value for a log file that has huge transactions, it fills out that space quickly and have to grow 64MB(your case) every time it fills. So good to increase as I mentioned.

Coming back to shrinking once you identified and removed the cause, you can shrink again. Understand that shrinking is simply releasing unused space(INACTIVE) to Disk. This will be effective after a backup as there will be more VLF's marked as inactive. You need to shrink only if Disk space issue is a Concern else after backup, space will be marked as reusable and log file reuses that for next transaction.

HI Shanky, Above question is posted by me.
I've full recovery model set on my DB. We are taking every 25 minutes as a transaction log backup.

I ran your script and it's giving me answer as a   log_reuse_wait_desc
                                                                       REPLICATION

Is there a lot of bulk processing going on?

Hey Jinu,

I've transaction log backup on my prod db is every 25 minutes.

Is there any script to set back ideal limit to transaction log file? PLease provide me step by step. Thanks

Jianasql12,

What is the size of your T-log file now ?

Are you running out of space on your LUN ?

1. Forecast the growth of your log file in last 1 month and make sure you have enough space on LUN to accommodate
2. Set the auto growth based on your DB growth as I mentioned in earlier post. Say 1024MB if it is growing rapidly. To run it as TSQL you can change these settings in GUI and script the action to new Query window.

• Transactional Replication and the Transaction Log

• Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database. If the transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs or setting it run in continuous mode. If it is set to run in continuous mode (the default), ensure that it is running.

Right now my T-log file size is 162gb which is never be like this before. i'm not out space but I will be in near future.

As I asked before do you have replication enabled ? If so/If not.. go thru what I have posted.

Since you said you have log backups happening every 25 minutes, what is the size of each backup file ? If its really big you would need to consider having more frequency like 15 minutes.

Hey Jinu,

I ran this script (select log_reuse_wait_desc from sys.databases where name='db_name') and it's giving me answer as a   log_reuse_wait_desc
                              REPLICATION         That means my replication is enabled, even though we are not using replication at all.

I used following command to removed replication(I'm using SSMS 2005) Is that correct?

EXEC

sp_removedbreplication

 

• Edited by JianaSql12 15 hours 53 minutes ago

You could try removing but before its always good to create one and then remove which would remove them completely. I believe This Db was restored from a replication enabled DB

EXEC

sp_removedbreplication

 it will remove replication and log will reuse.