Reducing size of Log File

[StevenK]

I recently backed up one of development databases and restored it onto my laptop.
The back-up file was about 110MB in size.
The restore option took a long while to run, but did run successfully (eventually).
On looking through Windows Explorer it is seen that the Database1.mdf file is about 135MB in size with the 'log' file Database1_log.ldf being a whopping 3.48GB.
My impression is that the log file contains database history that is no longer relevant to me.
How do I go about reducing this log file ?
I've tried running the DBCC SHRINKFILE command but it doesn't seem to impact the size of the 'ldf' log file.
Can someone offer me some advice here ?
I'm guessing it's something simlpe I need to do ....

Thanks in advance
Steve

 

[osjohnm]

Hi

I'm guessing that you are running SQL 2000 and that your database uses the FULL recovery model and that you don't backup your database frequently?

By default the FULL recovery model is used in SQL 2k unless SIMPLE or BULK-LOGGED are specified. Using FULL is the best as it writes every transaction into the log and supports transaction log backups and point-in-time restores.

THe only way to keep the log file small is to back it up often. Performing a full database backup will also shrink the inactive portion of a log. ANy active transactions will remain in the transaction log.

If you are using the FULL recovery model then do the following:

- perform a full database backup, this should shrink the inactive portion which should reduce the size dramatically.
- i would also suggest that you backup the transaction log more often, atleast every 2 hours. this will help to keep the file relatively small
- if the full db backup doesn't help then do the following:

...right click the database > properties > change the recovery model to SIMPLE.
then in query analyser run the following cmd against the datbase: dbcc shrinkfile (0,0)
go
then change the recovery model back to FULL and implement more tlog backups.

I recommend the following FAQs as well:

How Do I Develop a Backup and Restore Strategy?
faq183-1784

Shrinking Databases and Logs - SQL 7 and Higher
faq183-1534

hope this helps
John


db

 

[ecobb]

Since you said that you restored a development database to your laptop, I'm guessing that you're going to be using it for development as well. I would suggest doing a Transaction Log backup (don't worry about backing up the whole database), and once the backup is complete, if you don't need the log backup, delete it to free up some space. Backing up the log will truncate the existing log file and keep only the pending transactions.

And, as osjohnm suggested, if your going to be doing a lot of work in the database you should probably schedule Transaction Log backups to keep the log from growing.

Hope this Helps!