Large ldf file in SQL Server

Hi

In SQL Server 2000 we have an intranet database (which uses sharepoint) and the .ldf file is now over 50GB and the .mdf file is over 7GB.

The backup runs every night.
It's Recovery Model 'Full'

Is there anything I can do to make the files smaller?

I've seen advice to truncate the database with this command
"backup log dbname with truncate_only"

Any ideas?

Thanks
hmcgeehanAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Do I do it instead of a full backup?
no, in addition to the full backup.

you have to be aware why you do backups: it's to be able to restore.
with a full backup (only), you can only restore that one. all changes made since that full backup are lost.
there is where the transaction log backup comes into the play: with those backups, you can restore the database to ANY point in time from the full backup to the last transaction in the transaction log backups.

note: you must have ALL the transaction log backup since the full backup to the time you want to restore!


>My hard disk space is at a minimum now. >Less than 1GB left!

ok, do this:



backup log dbname with truncate_only;
checkpoint;
dbcc shrinkfile (your_log_file.ldf, truncate_only);
-- http://msdn.microsoft.com/en-us/library/ms189493.aspx --
 
 
you might need to repeat this, the shrink might not "work" on the first time.
once this freed up your disk space, run 1 full backup, and implement the transaction log backup ASAP

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>It's Recovery Model 'Full'
you don't see to be running NO transaction log backup, which means the log space in the .ldf file never get's reclaimable.

>I've seen advice to truncate the database with this command
don't. at least not regularly.
just implement regular transaction log backups (the first one will be big!), and once that is in place, you can try to shrink the .ldf file to a reasonable size.
0
 
hmcgeehanAuthor Commented:
Thanks!

So what do I need to do from here?
Implement a transaction log backup?
How do I do that?
Do I do it instead of a full backup?

My hard disk space is at a minimum now.
Less than 1GB left!

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
hmcgeehanAuthor Commented:
BTW should I check the 'auto shrink' option?
db-properties.bmp
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>BTW should I check the 'auto shrink' option?
NO. that will just waste I/O ....
0
 
hmcgeehanAuthor Commented:
Thanks

As this is a sharepoint database would we need to disable the website to ensure no attempts are made to update the database while we're doing the backup and shrink?

Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no. these operation can be done online.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.