The command Dbcc sqlperf(logspace) will be a great help in determining the
size of the log file and percentage use.
Select * from master..sysfiles will give an indication which are the log
files for the database.
if the log file percentage use is greater that 50 % you can try the
following command.
checkpoint
go
Backup log <datbase name> with truncate_only
go
If the log file is using 80% of the harddisk space then
Checkpoint
go
Backup log <database name> with Truncate_only
go
Dbcc Shrinkfile <Logical file name of the log having big size>
Other method will be fixing the maximum limit of the log file and adding a
log file to the database as required.
Deepak
-----Original Message-----
From: Bhamani, Naushad A [mailto:Naushad.[Email Address Removed] Wednesday, March 27, 2002 4:59 AM
To: 'Pranjal.[Email Address Removed] Naushad.[Email Address Removed] [Email Address Removed] 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] Wednesday, March 27, 2002 7:50 AM
To: Naushad.[Email Address Removed] [Email Address Removed] 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] ]
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] ]
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