Does the maint plan back up the log?
-----Original Message-----
From: Reed William L.
[mailto:mssqldba-ezmlmshield-x6810259.[Email address protected]
Sent: Thursday, June 29, 2006 2:02 PM
To: LazyDBA Discussion
Subject: RE: Want to shrink !!!
I've been working with a SQL 2005 DB that is being used by WebRoot. The
primary file is 200 MB. The log file on the other hand is getting huge!
16GB!!
The recovery model is set to Full and the Maint Plan runs every morning
at 1AM and does a Full Backup.
The log file seems to be out of control.
Any advice would be very helpful.
Thanks!
Bill
-----Original Message-----
From: Sam Rieta
[mailto:mssqldba-ezmlmshield-x26639165.[Email address protected]
Sent: Thursday, June 29, 2006 12:52 PM
To: LazyDBA Discussion
Subject: RE: Want to shrink !!!
Death to Auto Shrink!!!!
Of course I also do not like Auto Grow, if you do not monitor the size
of your DB and use Auto Grow, let it grow by MB, don't do percentage.
Questions or comments?
619-955-5000 x1052
-----Original Message-----
From: Bellizzi Christopher - IBG
[mailto:mssqldba-ezmlmshield-x20036562.[Email address protected]
Sent: Thursday, June 29, 2006 5:02 AM
To: LazyDBA Discussion
Subject: RE: Want to shrink !!!
Atta boy Hendrik! I love it. I am going to make a new video, "When DBA's
Attack". Of course I will have a LARGE role.
Christopher Bellizzi
SQL DBA/Systems Analyst
240 Fall Street
SFO Main Office
Seneca Falls, NY 13148
315-568-7886
-----Original Message-----
From: Henrik Staun Poulsen
[mailto:mssqldba-ezmlmshield-x52624959.[Email address protected]
Sent: Thursday, June 29, 2006 2:16 AM
To: LazyDBA Discussion
Subject: RE: Want to shrink !!!
Hi Frank,
Absolutely.
We had a db with Auto Shrink. It almost killed the programmer, because
there were dead-locks and timeouts everywhere when the db tried to grow
again.
Once Auto shrink was off, things worked well.
Best regards
Henrik Staun Poulsen
-----Original Message-----
From: Frank Lau/IT
[mailto:mssqldba-ezmlmshield-x89806827.[Email address protected]
Sent: 27. juni 2006 18:15
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
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
************************************
This e-mail and any files transmitted with it are proprietary and
intended solely
for the use of the individual or entity to whom they are addressed. If
you have
received this e-mail in error please notify the sender. Please note that
any views
or opinions presented in this e-mail are solely those of the author and
do not
necessarily represent those of ITT, Inc. The recipient should check
this e-mail and any attachments for the presence of viruses. ITT accepts
no liability for any damage caused by any virus transmitted by this
e-mail.
************************************
---------------------------------------------------------------------
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