<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Mike Pearon
»
Auto Close and Auto Shrink - Just Don't
19 posts, Page 1 of 2
1
2
»»
Auto Close and Auto Shrink - Just Don't
Rate Topic
Display Mode
Topic Options
Author
Message
Mike Pearson
Mike Pearson
Posted Friday, May 02, 2003 12:00 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, September 04, 2007 4:36 AM
Points: 27,
Visits: 1
Comments posted to this topic are about the content posted at
http://www.sqlservercentral.com/columnists/mpearson/autocloseandautoshrinkjustdont.asp
Post #11890
chrisleonard
chrisleonard
Posted Monday, May 05, 2003 8:23 AM
SSC Eights!
Group: General Forum Members
Last Login: Sunday, November 14, 2010 9:42 PM
Points: 968,
Visits: 65
Another problem with autoshrink is simplf that it moves data. You might work very hard to make sure your tables, indexes, etc., get built contiguously only to have autoshrink make mincemeat of all your hard work. Like the author says: no thanks.
One possible exception: a sandbox / desktop server where space really *might* matter that much and performance might *not*.
Post #61214
Dave Gall
Dave Gall
Posted Monday, May 05, 2003 9:26 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, July 26, 2010 5:42 PM
Points: 9,
Visits: 48
I agree regarding auto close, but disagree regarding auto shrink. Reason: I have many databases to deal with some of which grow their logs very fast eating up valuable disk space. Auto shrink ensures that unused space is returned to the pool. I feel that the CPU performance hit is acceptable compared to the problems ensuing when we run out of disk space.
Post #61215
Andy Warren
Andy Warren
Posted Monday, May 05, 2003 4:32 PM
SSCertifiable
Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462,
Visits: 1,384
Dave, I agree that leaving space tied up may not be a good thing, but wouldn't you consider it better to either do after hours or at a time when you knew it would least impact performance?
Andy
http://www.sqlservercentral.com/columnists/awarren/
Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter
Post #61216
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Monday, May 05, 2003 4:58 PM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 6:14 PM
Points: 31,421,
Visits: 13,734
Just Say No. Control when the shrinking takes place if you need to worry about it. Though I'd say if you are tight on disk space, you've got other issues.
Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones
www.dkranch.net
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #61217
ckempste
ckempste
Posted Monday, May 05, 2003 7:12 PM
SSC Eights!
Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885,
Visits: 1
Hi there
Experience tells me to manage it yourself in terms of shrinking (when it happens, what is going on and sync it with other jobs that may be going on). The trick to watch a little more carefully I believe is your growth settings and not chewing space that perhaps wont be used for sometime. Managing transaction log space utilisation is another tricky one, especially if you have the occasional very large job that blows out the log size.
Btw, I havent measured the performance hit whilst files are expanding or shrinking during heavy IO... I like to monitor growth and get a feel for this to ensure files cater for the expansion (reserved space) without sqlserver working to grab more space.
The close option is a silly one and I never set it. I have come across some issues of connections locking up and blocking because of it. Its probably been resolved between versions etc etc but I cant see any logical value in it.
Cheers
Chris K
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Post #61218
spaski
spaski
Posted Tuesday, May 06, 2003 2:06 AM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, August 15, 2006 12:17 AM
Points: 10,
Visits: 1
With the whole shrinking we use truncating of the transaction log as well.
At the end of the day(during normal at hours-middle of the night) we do all the shrinking etc, but we truncate the transaction log. This saves alot of space for backup purpose.
Is this bad good or ugly.
Cheers
Post #61219
ckempste
ckempste
Posted Tuesday, May 06, 2003 3:14 AM
SSC Eights!
Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885,
Visits: 1
I would shrink during a quiet time (if there is any) for your DBMS, get a feel for the average times taken and move the times around from there.
As for the truncation of the logs, so long as you dont require point in time (PTR) recovery, then its a no issue.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Post #61220
Mike Pearson
Mike Pearson
Posted Tuesday, May 06, 2003 4:36 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, September 04, 2007 4:36 AM
Points: 27,
Visits: 1
RE truncating the Logs - if you're going to go to the effort of truncating 'em - why not just set the database to simple logging and be done with it?
RE Dave comment's about AutoShrink - Dave - can you not set up an alert+job to backup the transaction log when it get's to (say) 80% capacity? That way you don't have the CPU hit of a log file that is constantly growing and shrinking...
Post #61221
AFPeterson
AFPeterson
Posted Tuesday, May 06, 2003 5:29 AM
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, March 01, 2005 11:41 AM
Points: 258,
Visits: 1
For production system, I agree, just say no. Manage space usage based on the business needs (slow time) and don't rely on Auto Shrink.
I've found some "production" systems such as Crystal Reports that use MSDE with auto close enabled. so review every install, and do an inventory of all your production system if you need to.
What's the business problem you're trying to solve?
Post #61222
« Prev Topic
|
Next Topic »
19 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
