Leave it off.
From http://www.sql-server-performance.com/database_settings.asp
Many databases need to be shrunk periodically in order to free up disk
space as older data is deleted from the database. But don't be tempted
to use the "auto shrink" database option, as it can waste SQL Server
resources unnecessarily.
By default, the auto_shrink option is turned off, which means that the
only way to free up empty space in a database is to do so manually. If
you turn it on, SQL Server will then check every 30 minutes to see if it
needs to shrink the database. Not only does this use up resources that
could better be used elsewhere, it also can cause unexpected bottlenecks
in your database when the auto_shrink process kicks in and does its
work.
Wes Wilson
REGISTRAT, Inc.
Sr. Systems Administrator / Data Base Administrator
-----Original Message-----
From: Frank Lau/IT
[mailto:mssqldba-ezmlmshield-x89806827.[Email address protected]
Sent: Tuesday, June 27, 2006 12:15 PM
To: LazyDBA Discussion
Subject: RE: Want to shrink !!!
Are there any other DBAs do not recommend "Auto Shrink" because the
performance will be degraded?
Thanks,
Frank Lau
-----Original Message-----
From: Jay Butler [mailto:[Email address protected]
Sent: Tuesday, June 27, 2006 8:58 AM
To: [Email address protected]
Subject: RE: Want to shrink !!!
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email, **and removed
any attachments, and kept your email address secret **from this person,
and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal **Anything below this line is the original email text
Auto shrink = auto fragment. Do not do it on a production database.
Performance will degrade while SQL Server is auto shrinking (and you
have no control over when that is). It also creates a huge amount of
file fragmentation.
From: Frank Lau/IT
Sent: Tue 27-Jun-06 11:53
To: LazyDBA Discussion
Subject: Want to shrink !!!
What is the proper procedure to shrink the databases?
In the property of each database, there is an option for me to configure
"Auto" for shrinking databases, can I simply configure to that option?
Do I need to back up first before I configure to that option?
Thanks,
Frank Lau
-----Original Message-----
From: Wes Wilson
[mailto:mssqldba-ezmlmshield-x86275825.[Email address protected]
Sent: Tuesday, June 27, 2006 8:17 AM
To: LazyDBA Discussion
Subject: RE: Urgent for a solution
No, Ralph was right.
From BOL:
Bulk-Logged Recovery
The Bulk-Logged Recovery model provides protection against media failure
combined with the best performance and minimal log space usage for
certain
large-scale or bulk copy operations. These operations are minimally
logged:
SELECT INTO.
Bulk load operations (bcp and BULK INSERT).
CREATE INDEX (including indexed views).
text and image operations (WRITETEXT and UPDATETEXT).
In a Bulk-Logged Recovery model, the data loss exposure for these bulk
copy
operations is greater than in the Full Recovery model. While the bulk
copy
operations are fully logged under the Full Recovery model, they are
minimally logged and cannot be controlled on an operation-by-operation
basis
under the Bulk-Logged Recovery model. Under the Bulk-Logged Recovery
model,
a damaged data file can result in having to redo work manually.
In addition, the Bulk-Logged Recovery model only allows the database to
be
recovered to the end of a transaction log backup when the log backup
contains bulk changes. Point-in-time recovery is not supported.
In Microsoft SQL Server(tm) 2000, you can switch between full and
bulk-logged recovery models easily. It is not necessary to perform a
full
database backup after bulk copy operations complete under the
Bulk-Logged
Recovery model. Transaction log backups under this model capture both
the
log and the results of any bulk operations performed since the last
backup.
The backup strategy for bulk-logged recovery consists of:
Database backups.
Differential backups (optional).
Log backups.
Backing up a log that contains bulk-logged operations requires access to
all
data files in the database. If the data files are not accessible, the
final
transaction log cannot be backed up and all committed operations in that
log
will be lost.
Wes Wilson
REGISTRAT, Inc.
Sr. Systems Administrator / Data Base Administrator
-----Original Message-----
From: Pedro Manuel Oliveira Rocha
[mailto:mssqldba-ezmlmshield-x73376718.[Email address protected]
Sent: Tuesday, June 27, 2006 10:50 AM
To: LazyDBA Discussion
Subject: RE: Urgent for a solution
I am sorry, but bulk logged, logs every trans like full model does, and
also
bulk inserts too... so it's the FULLEST Model you can choose!
hope it helps!
-----Original Message-----
From: Davis Ralph
[mailto:mssqldba-ezmlmshield-x22875020.[Email address protected]
Sent: tera-feira, 27 de Junho de 2006 15:06
To: LazyDBA Discussion
Subject: RE: Urgent for a solution
If you set it to bulk-logged, instead of simple, you won't have to take
another full backup. It will still write to the log but not nearly as
much
data as full recovery model does.
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Gordon Rayburn
[mailto:mssqldba-ezmlmshield-x41457154.[Email address protected]
Sent: Tuesday, June 27, 2006 8:51 AM
To: LazyDBA Discussion
Subject: RE: Urgent for a solution
Make sure you take a full backup after you reset your recovery back to
FULL.
-----Original Message-----
From: Michael Phillips
[mailto:mssqldba-ezmlmshield-x56897612.[Email address protected]
Sent: Monday, June 26, 2006 6:58 PM
To: LazyDBA Discussion
Subject: RE: Urgent for a solution
You probably have recovery mode on the db set to Full and you tried to
shrink the MDF's, but then all that cool shrinking was logged by
default.
If so you could run the following in Query Analyzer in an emergency
(backup
first). This worked for me.
alter database [YourDBName] set recovery simple
dbcc shrinkfile (YourDBName_Log)
checkpoint
dbcc shrinkfile (YourDBName_Log)
alter database [YourDBName] set recovery full
Michael
-----Original Message-----
From: Frank Lau/IT
[mailto:mssqldba-ezmlmshield-x66391863.[Email address protected]
Sent: Monday, June 26, 2006 6:28 PM
To: LazyDBA Discussion
Subject: Urgent for a solution
The transaction log of SQL 2005 is growing too fast, anyone can provide
some
tips??
Frank Lau
-----Original Message-----
From: Lucey Christine
[mailto:mssqldba-ezmlmshield-x30963954.[Email address protected]
Sent: Monday, June 26, 2006 9:08 AM
To: LazyDBA Discussion
Subject: RE: Database shrinking
Hi, Frank. Quick question: Is it actually your database that is growing
too
fast, or the database's transaction log?
Another question: What version of SQL are you running here?
--Chris
Christine H. Lucey MCSE MCP+I CCNA
Assistant Director of Network Services
The Kingston Hospital
396 Broadway, 4th Floor
Kingston, New York 12401
Tel. (845) 334-2700, ext. 2406
[Email address protected]
-----Original Message-----
From: Frank Lau/IT
[mailto:mssqldba-ezmlmshield-x63035230.[Email address protected]
Sent: Monday, June 26, 2006 12:04 PM
To: LazyDBA Discussion
Subject: Database shrinking
Hi all,
When one of the databases is growing too fast within the a hard drive,
what
kind of the solutions do you guys recommend to make sure that there are
always enough spaces for the databases??
I want to shrink the database, do you guys think it is ok to do so? What
is
proper procedure to shrink a database and prevent the lost of the
data???
Thanks,
Frank Lau
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
NOTICE: This transmission may contain confidential health information
that
is protected by special federal or state law or regulation. It is
intended
only for the use of the individual to whom it has been addressed. Only
the
person named in this transmission is authorized to view any information
contained herein. Re-disclosure without proper consent is prohibited.
Unauthorized use or disclosure or failure to maintain confidentiality
may
subject you to penalties under both federal and state law.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
------------------------------------------------------------------------
---------------------------
NOTICE OF CONFIDENTIALITY
The information contained in this communication and any accompanying
document(s) is proprietary and confidential and is intended solely for
the
above-named individual or entity. If you are not the intended receiver,
recipient or entity, you are advised that any distribution, copying,
disclosure or communication of this email is strictly prohibited. If you
have received this email in error, please contact me at the telephone
number
listed above or 858.716.1500.
========================================================================
======
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is
intended solely for the person or entity to which it is addressed. Any
review, retransmission, dissemination, or taking of any action in
reliance
upon this information by persons or entities other than the intended
recipient is prohibited. If you received this in error, please contact
the
sender and delete the material from any computer.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page