Naushad's point is good - that avoiding the need to shrink the log at all
would be preferable to any technique for shrinking it (and he's also right
that there are milestone log sizes which once passed become the minimum
value to which you can shrink back at v7 sp3).

However the recommendations include setting 'truncate log on checkpoint'. If
Meg needs to be able to recover the database to a point in time, then she
will need to able to roll forward from the logs; in that case 'truncate log
on checkpoint' must not be set. Frequent log backups would probably achieve
the same objective. Occasionally applications come up with single statements
(unqualified deletes can be a cause) which expand the log well beyond normal
requirements; shrinking then becomes the only option - apart from living
with a high water mark log. Without 'knowing' an answer; I think I'd incline
towards a procedure which ran dbcc sqlperf to ascertain log size followed by
dbcc shrinkfile if maximum preferred log size was exceeded. This could be
scheduled for regular execution via sql agent.

> -----Original Message-----
> From: Bhamani, Naushad A [SMTP:Naushad.[Email Address Removed] Sent: Wednesday, March 27, 2002 12:59 PM
> To: 'Pranjal.[Email Address Removed] Naushad.[Email Address Removed] [Email Address Removed] [Email Address Removed] Subject: RE: Shrinkfiles
>
> Yeah that's true but Meg needs a permanent solution to avoid often
> shrinking. this will prevent the growth, I have sql7 (sp3) and have
> tried
> lot of things to reduce the log size and file size but was unsuccessful.
>
> Not sure if this is a bug but will try to dig into as soon as I get chance
>
>
> -----Original Message-----
> From: Pranjal.[Email Address Removed] [mailto:Pranjal.[Email Address Removed] Sent: Wednesday, March 27, 2002 7:50 AM
> To: Naushad.[Email Address Removed] [Email Address Removed] [Email Address Removed] Subject: RE: Shrinkfiles
>
>
>
> But this will not shrink the data files. It will only truncate the log. To
> shrink the data files to its actual size(if there is a lot of unused space
> in data files) use dbcc shrinkdatabase command
>
> -----Original Message-----
> From: Bhamani, Naushad A [ mailto:Naushad.[Email Address Removed] <mailto:Naushad.[Email Address Removed] ]
> Sent: Wednesday, March 27, 2002 6:18 PM
> To: 'Meg Hardy'; SQLDBA (E-mail)
> Subject: RE: Shrinkfiles
>
>
> Best way is to set 2 options on the database and often take log backups
>
> use
> sp_dboption db_name, "trunc log on chkpt", false
> go
> sp_dboption db_name, "select into", false
> go
> use db_name
> go
> checkpoint
> go
>
>
> then do often log dumps.
>
>
> -----Original Message-----
> From: Meg Hardy [ mailto:[Email Address Removed] <mailto:[Email Address Removed] ]
> Sent: Wednesday, March 27, 2002 4:56 AM
> To: SQLDBA (E-mail)
> Subject: Shrinkfiles
>
>
> Hi there,
>
> Can anyone offer me some advise on shrinking files. We have a large
> database (sql 7.0) and have chosen not to have autoshrink on. What would
> you recommend as practice to deal with shrinking if the autoshrink is
> turned
>
> off?
>
> I'm new to all this and am getting very confused!!!
>
> Thanks
> Meg
>
>
> **********************************************************************
> DISCLAIMER
> Any opinions expressed in this email are those of the individual and not
> necessarily of Mondial Assistance (UK) Ltd. This email and any files
> transmitted with it, including replies and forwarded copies (which may
> contain alterations) subsequently transmitted from the Company, are
> confidential and solely for the use of the intended recipient. If you are
> not the intended recipient or the person responsible for delivering to the
>
> intended recipient, be advised that you have received this email in error
> and that any use is strictly prohibited.
>
> If you have received this email in error please notify the IT support desk
>
> by telephone on +44 (0)208 2393984 or via email to
> [Email Address Removed] including a copy of this message.
> Please
> then delete this email and destroy any copies of it. Thank you.
>
> Mondial Assistance (UK) Ltd
> Mondial House
> 102 George Street
> Croydon
> CR9 1AJ
>
> Mondial Assistance (UK) Ltd Registered in England No.1710361 Registered
> office as above
> **********************************************************************
>
>
> This message has been checked for all known viruses by the
> MessageLabs Virus Scanning Service. For further information visit
> http://www.messagelabs.com/ <http://www.messagelabs.com/>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: mssqldba-[Email Address Removed]
> For additional commands, e-mail: mssqldba-[Email Address Removed]
>
MS Sql Server LazyDBA home page