How do I use the Simple recovery model to shrink a log file?

[RRinTetons]

My log file was big and I needed the disk space back, I did the backups and then ran my trusty log backup with truncate only. It worked as usual, but I was chastised in the sql log with the notation that that approach is deprecated and I'm now supposed to use the simple recovery model to automatically shrink the transaction log.

What does that mean? What's the SQL Server 2005 approved method of shrinking the log file to a minimum size?

-
Richard Ray
Jackson Hole Mountain Resort

 

[mrdenny]

The best way to keep the log small is to backup the transaction log on a regular basis.

If you don't need to be able to restore the database to a specific point in time you can change the database to the simple recovery model. Right click on the database and select properties. You can then change the recovery model from FULL to SIMPLE.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[RRinTetons]

Does that immediately shrink the log file the way the TRUNCATE ONLY approach does?

In this case I added some steps to the MP that ballooned the log file unexpectedly and I had to get it back down to a level where the database could become operational again. I had a full backup and a tran log backup, but the tran log hadn't reduced its size as of yet. (I believe that takes awhile for the recovery to occur?)

If I understand what you're saying, then at that point I should just put the DB into Simple recovery mode and then back into Full? That's it?!?!

As far as backing up the tran log regularly: Up until now I've run a 15 minute tran log backup job from 6 AM to 9 PM (active operations) and then shut it down while various overnight posting jobs, ETL jobs and other maintenace occur. It looks like I need to keep running the tran log backup now, in order to keep the log size under control. Can you point me to some references on how to schedule that around full backup jobs, index maintenance, etc.? Or, do I just let the job run every 15 minutes and let SQL Server manage the conflicts? (Production full back up takes 45 minutes to an hour, ETL another hour, index maintenance 45 minutes, etc.)

-
Richard Ray
Jackson Hole Mountain Resort

 

[mrdenny]

Just have the backup run every 15 minutes all the time. The only conflict will be with the full backup.

The file won't shrink by it self, it will only empty. You will need to use the DBCC SHIRNKFILE to make the file smaller.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog