Urgent Help Needed! Transaction Logs

[jr1vas]

Good Morning Everyone,
I need urgent help. I would like to find out if any of you have suggestions on how to keep the transaction logs from growing so big.

For example:

There is a .mdf file that's about 2gigs and the .ldf file is over 10gigs now!!

What would be some of the suggestions for keeping the log from growing??

How about a database that is 10gigs in size that the .ldf keeps growing to over 25gigs?

Thank you so much and I'm looking forward to as many suggestions as possible.

 

[nigelrivett]

Are you taking tr log backups?
If not then set the recovery model of the databases to simple. That will allow it to re-use space.

It could be because your system uses large or long running transactions - if so then there is nothing you can do except re-design.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBill]

Are you even backing up your database at all?

-SQLBill

 

[jr1vas]

Yes, complete backups are running and the recovery model being used is full because transaction log backups are also running. But still the .ldf file keeps growing!

Do you guys think that taking a complete backup shrinks the .ldf down? Do you guys think that shrinking the database will help?

I am just trying to get as many ideas as possible on what to try out. I'm seeing this problem happening more and more often.

Thanks guys!!!

 

[nigelrivett]

No backups will shrink the ldf.
A tr log backup will allow entries to be re-used but you will have to shrink the file explicitly - see dbcc shrinkfile.

If it is just going to grow again then there is no point though. You may have to backup the log more frequently or look at long running / large transactions.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBill]

As Nigel says, look at large transactions. If you have any large transactions (you'll have to determine what is large), you might benefit from using BULK INSERT.

-SQLBill