why ldf file suddenly growing? what is the cause?

hi we are using SQL Server 2005, Last Two days faced space issue in our Server drive.

After complete analysis the LDF file size is 21 GB.

But normally it will be in 190-300 MB Only.

No Bulk update/insert taking place.

So can any one tell me

what is the cause of sudden increase of LDF file and how to stop growing and prevent measure to follow in future??

more ▼

asked Dec 04, 2012 at 09:38 AM in Default

avatar image

2.5k 72 76 83

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Check the value of log_reuse_wait_desc in sys.databases : http://msdn.microsoft.com/en-us/library/ms178534%28v=sql.90%29.aspx

more ▼

answered Dec 04, 2012 at 09:47 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

thanking you kev reiley ..

select log_reuse_wait,log_reuse_wait_desc,* from sys.databases after I was executing,

I have noticed that problem of one db in LOG_BACKUP and another temp db in

ACTIVE_TRANSACTION value in log_reuse_wait_desc column in sys.databases table.

how can i see which transaction in temp db still wait in log??

how can i solve db in log_backup in log_reuse_wait_desc? as u give articl suggest to make

two backups can solve the log_backup issue is nt???

Dec 04, 2012 at 10:19 AM askmlx121

Don't worry about the tempdb row, just look at the database that has the LDF issue.

Is that the one with LOG_BACKUP as the value for log_reuse_wait_desc? Have you done 2 LOG backups?

Dec 04, 2012 at 12:05 PM Kev Riley ♦♦

yes kev riley log_reuse_wait_desc column value is LOG_BACKUP.

I did nt take 2 log bakcups.

But i have read http://msdn.microsoft.com/en-us/library/ms178534%28v=sql.90%29.aspx

It advise to take 2 log backups.

why should i take 2 log backups? how is it solve the issue? explain me..

Dec 04, 2012 at 01:01 PM askmlx121

2 backups are often needed if no log backup has ever been taken

Dec 04, 2012 at 02:14 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

which recovery mode is used run this query to find out recovery model of your database select name, recovery_model_desc from sys.databases where name like 'dbname'

if you database is in FULL recovery mode, then how frequently do you take transaction log backup.

generally when there is high number of transaction, it may not be user level transaction but maintenace plans or jobs might be the reason for ldf file size increase

more ▼

answered Dec 05, 2012 at 06:55 AM

avatar image

ramesh 1
2.2k 66 69 73

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 04, 2012 at 09:38 AM

Seen: 2908 times

Last Updated: Dec 05, 2012 at 06:55 AM

Copyright 2018 Redgate Software. Privacy Policy