LDF FILES ARE EXTREMELLY HIGH

[sereleg]

I have a sql server 2000 database with its LDF file incresing day by day. Could some one help me to solve this situation the size is almost 10GB and the server is almost full.

Thanks

 

[NoCoolHandle]

This could probably be fixed by doing a full backup followed by a transaction log backup.

(or maybe just a tl backup. Then you should wait for a an hour or so before shrinking the database...)

 

[SQLBill]

You probably aren't doing backups. Shame on you. Do a full backup immediately. Then do a log backup. Once all that's complete, as NoCoolHandle says, you can do a DBCC SHRINKDB or DBCC SHRINKFILE.

Refer to the BOL for more information on BACKUP DATABASE, BACKUP LOG, DBCC SHRINKDB, and DBCC SHRINKFILE commands. Also, this subject has been covered too many times in this forum. Please do a search and I'm sure you will find lots of information. Take a look through the FAQs also.

-SQLBill

Posting advice: FAQ481-4875

 

[NoCoolHandle]

Bill my memory says that until you do your first full backup the database pretends it is in truncate on checkpoint mode....(TL's don't grow)

So they probably did a full backup but haven't done any tl backups...

Just a guess.. Been a while since I went down that track.

Rob

 

[SQLBill]

Could be.....I backed up my db as soon as it was created, so I'm not aware of the issue you bring up. Sereleg, if you have been doing Full backups, please accept my apologies. I was out-of-line assuming that you haven't done any.

-SQLBill

Posting advice: FAQ481-4875

 

[NoCoolHandle]

However I don't backup my db's so what do I know

insert your rant here

 

[Danster]

Dunno 'bout others, but as a matter of course I do either a nightly or weekly job with three steps...

1. Backup trans log
2. Backup trans log with truncate only
3. DBCC SHRINKFILE('tran_log', truncateonly)

If the LDF is huge, you need enough spare HDD space to backup the trans log the first time equal to how big the LDF is. Subsequent runnings should not need so much hard disk space.

Another way to shrink the log file is to 1. Backup the database. 2. Detach database. 3. Delete the LDF 4. Reattach with a new LDF. I personally don't like that method, but it seems to work OK every time.

cheers

Danster

 

[SQLBill]

Danster,

Please tell me you do a FULL backup once a day.

When you TRUNCATE the log, it 'breaks' the chain of logs. You can not apply following logs to the last FULL backup.

This is from the BOL:

Although the transaction log may be truncated manually, it is strongly recommended that you do not do this, as it breaks the log backup chain. Until a full database backup is created, the database is not protected from media failure. Use manual log truncation only in very special circumstances, and create a full database backup as soon as practical.

-SQLBill

Posting advice: FAQ481-4875