DB Log File Size

acrouch · Mar 20, 2003

I have a Log file for one of my SQL DB's that is over 5Gig and growing daily. The DB itself is no where near that size.
The Database is backed up every night as part of a maintenance plan which backs up multiple Databases and this is the only Database I seem to have a problem with.

I have restored the Database and still no difference, I created a new database and restored a backup to this and the Log file did shrink but 2 days latter it was back up at 3gig.

I am running MSSQL 2000.

Does anyone have any ideas ?

Thanks in advance for your help !

SemperFiDownUnda · Mar 20, 2003

you need to truncate your log when you do your full backups. Or do transaction log backups inbetween your main backups and truncation with them.

tlbroadbent · Mar 21, 2003

See faq183-1534 - Shrinking Databases and Logs - SQL 7 and Higher.
If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

boopers · Mar 21, 2003

This may not be unusual depending on how your application was designed. I also have an app whose logs grow to several gigs over a week's time, and the dbase isn't nearly as large.
I make sure I restart everything at least once a week to keep the size down. Also, make sure on your maintenance plan that you only keep 2 or so copies of the transaction logs and full backups, then delete after that. Otherwise your drive will fill up. Truncating will also help keep the size down as the others said.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

DB Log File Size

acrouch

Technical User

SemperFiDownUnda

Instructor

tlbroadbent

MIS

boopers

MIS

Similar threads

Part and Inventory Search

Sponsor