Log File (ldf) exceeds to huge value

DB logs  file exceeded 80GB limits and shut down the application. It is happening from weekly to 3 times per week to nightly
File size was 6 GB from Monday night to Tuesday morning 80 GB in 36 hours with current number of transactions

Is there way to find out what is the root cause of the issues.

will ldf file analysis will help us . The database has been set to full recovery model(Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) )


mdf file (3295 mb) ,By 1 MB , unrestricted growth
ldf file 3402  mb  ,By 10 percent ,restricted growth to 2097152 MB

how do we decrpyt ldf files to find out the root cause. Any DMV (select * from ::fn_dblog(null,null)) .

Please help .

February 2nd, 2015 4:44am

ldf file 3402  mb  ,By 10 percent ,restricted growth to 2097152 MB

First you should Change the autogrowth value from percentage to a fix value instead, e.g. to 100 MB.

How often are you running log backups?

Free Windows Admin Tool Kit Click here and download it now
February 2nd, 2015 4:55am

The Infastructure Team has impletemented log shipping. 
February 2nd, 2015 5:00am

As Olaf said, first change autogrowth to MB's , keeping it in % is going to be more problematic.

You cannot find out why tthe tlog growth happened, rather than you finding out what all happened during that time frame and assuming it would have caused the issue.

The only really way to find it out is to check this query. You need to check this when the log file is growing.

select name, log_reuse_wait_desc from sys.databases

For that particular database, log_reuse_wait_desc will clearly tell you the reason the log is not being used and that is the reason why it needs to grow. Check this for all the different factors which can cause this

http://msdn.microsoft.com/en-gb/library/ms190925.aspx#FactorsThatDelayTruncation

Taking regular transaction log backups help, unless you dont have other factors which would stop the log from reusing like, replication, mirroring, long running transactions etc.

Free Windows Admin Tool Kit Click here and download it now
February 2nd, 2015 5:14am

We dont have the access to change the ldf file from percentage to fixed value . The Infastructure team will provide at the max the ldf file
and is there any way (say T-SQL or DMV ) which reads the ldf file and provide the details for abrupt increase of ldf file.
February 2nd, 2015 5:27am

Read this link and try your luck , if at all you can come up with how to find it- http://rusanu.com/2014/03/10/how-to-read-and-interpret-the-sql-server-log/

But I dont think there is an easy way to find why the ldf file has to be grown or it can be found at all. If at all there was a way many DBA's life would have been simp

Free Windows Admin Tool Kit Click here and download it now
February 2nd, 2015 6:28am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics