Full and Shriking Logs

25 Aug 2006 6:11 PM

CLM

Question: I have some 2000 databases that are in FULL mode and get log
backups. The get a full backup in the early evening and then backup with a
few log backups throughout the day.

They are growing to a size that is uncomfortably large (for the drive) and
so I'd like to shrink them after the last log backup. What is the best way
to do this?

Note: I'd like to script it so I can schedule it periodically.

One way I thought of doing it is to build a script that does 1) alter table
to SIMPLE, 2) shrinks the log and 3) alter table to FULL. Is this wise
though and is it a solid way to do it? Or is there an easier way?

25 Aug 2006 10:20 PM

Hugo Kornelis

On Fri, 25 Aug 2006 11:11:01 -0700, CLM wrote:

>Question: I have some 2000 databases that are in FULL mode and get log
>backups. The get a full backup in the early evening and then backup with a
>few log backups throughout the day.
>
>They are growing to a size that is uncomfortably large (for the drive) and
>so I'd like to shrink them after the last log backup. What is the best way
>to do this?
>
>Note: I'd like to script it so I can schedule it periodically.
>
>One way I thought of doing it is to build a script that does 1) alter table
>to SIMPLE, 2) shrinks the log and 3) alter table to FULL. Is this wise
>though and is it a solid way to do it? Or is there an easier way?

Hi CLM,

Don't do this. If the log files grow to a certain size, then they
oobviously need that size. Shrinking will give you some short-term
relief, but they'll have to grow back to their original size at a later
time since they need that size.

If you really need snmalle log files, schedule your log backups more
often. Then, you can shrink the log files once, then allow them to grow
to what now their normal working size is.

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

--
Hugo Kornelis, SQL Server MVP