Solved

which are the risks of disable autoshrink on SQL-Server ?

Posted on 2010-09-02
21
797 Views
Last Modified: 2012-06-21
Dear Expert:

We have an SQL-Server 2000 SP3 in production machine. Sometimes performance slows down and we have seen it's when SQL-server starts an autoshrink process.

We read some articles on internet which says autoshrink slows down the database, so we are thinking about unset this option. The question is: if we uncheck this autoshrink option, could we have problems on backup/restoring database or maybe on our storage system ?
What risks should we consider ?

Thank you.
0
Comment
Question by:gplana
  • 10
  • 6
  • 3
  • +2
21 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 100 total points
Comment Utility
There is no risk in disabling the auto shrink.
You can set a job to do the autoshrinking once in a while when the server is not highly used otherwise you just need to monitor your system and make sure you do not run out of disk space
0
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 200 total points
Comment Utility
Using autoshrink is a very bad idea anyway.  Disable it.  For what it's worth, you should never need to shrink a database.
0
 
LVL 7

Assisted Solution

by:rashmi_vaghela
rashmi_vaghela earned 100 total points
Comment Utility
If you properly size your MDFs and LDFs, then you should
never have to shrink a file.
• Don’t schedule database or file shrinking operations.
• If you must shrink a database:
– Do so manually
– Rebuild the indexes after the shrink is complete
– Schedule these steps during the slow time of the day
• Benefits of not automatically shrinking files:
– Eliminates grow and shrink syndrome
– Reduces physical file fragmentation
– Reduces resources
0
 
LVL 15

Author Comment

by:gplana
Comment Utility
We don't have a lot of free space (in fact, we have a very little free space on hard disk). So, how many will grow our datafile and logfile if I disable autoshrink ?
0
 
LVL 15

Author Comment

by:gplana
Comment Utility
All parameters on our SQL-Server 2000 are the default parameers when you install it.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
Comment Utility
>>So, how many will grow our datafile and logfile if I disable autoshrink ?<<
Do not use Auto-Shrink.  Period.

>>All parameters on our SQL-Server 2000 are the default parameers when you install it.<<
Auto-Shrink is not enabled by default.
0
 
LVL 15

Author Comment

by:gplana
Comment Utility
Thank you but I already have this question> how mny will grow our datafile and logfile if I disable autoshrink ?
I think our database is in basic full mode because I've seen log file is small
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>how mny will grow our datafile and logfile if I disable autoshrink ?<<
I am not following you.  Your original question had to do with what are the risks associated to Auto-shrink.  In a nut-shell, if performance is a problem than do not enable it.  On the other hand if this database is to maintain your wife's cooking recipes than you probably will not notice any problems.
0
 
LVL 15

Author Comment

by:gplana
Comment Utility
This database is the production database f an hospital. The problem is the diskspace is amost full, so I want to be sure if I put off autoshrink option I don't have the risk to became without disk space and the database server of the hospital goes down.
I think the queston is very relevant....
0
 
LVL 17

Expert Comment

by:Chris Mangus
Comment Utility
If your application is this critical then you need to buy more disk space!  Autoshrink isn't going to solve any problems.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 15

Author Comment

by:gplana
Comment Utility
I understand. But my question is if we uncheck autoshrink option, will the log file or data file grows more than it's growing actually with autoshrink checked ?
0
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 200 total points
Comment Utility
No, it's going to grow the same either way.  The only difference here is that you're "hiding" the growth that it's doing.

It can still grow up to the point of available disk space ever with autoshrink turned on unless you've set maximum growth size for your databases.  And, with autoshrink on you're also going to compound your performance problems.

It sounds like you're running on borrowed time and should be getting more disk space sooner rather than later.
0
 
LVL 15

Author Closing Comment

by:gplana
Comment Utility
Thank you all of you for your comments.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>Thank you all of you for your comments.<<
I am sorry you did not like the answers we provided.  However that is no excuse for a "B" grade.  Please re-read the EE Guidelines on grading.
0
 
LVL 15

Author Comment

by:gplana
Comment Utility
I put a B because there are no lnks to articles from Microsoft which demostrate what all of you say. However, I appreciate all your opinions and I have a point to investigate.
I'm sorry that you dislike the qualification I put.
Regards.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>I'm sorry that you dislike the qualification I put.<<
It is not a question of dislike.  It is just sad when someone who has been an EE member for a number of years is incapable of following the EE Guidelines on grading.

Good luck.
0
 
LVL 15

Author Comment

by:gplana
Comment Utility
Could you please provide me the link to the EE Guidelines on grading you are referring to ?

Thank you.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Sure.
http://www.experts-exchange.com/help.jsp#hs=29&hi=403
What's the right grade to give?
Grading at Experts Exchange is not like school. It's more like the "10-point Must" system in professional boxing; in other words, an answer is worth an A, unless it doesn't resolve your issue. If it requires you to do a little more research, or figure out one more piece of code, then it's worth a B. If you think it's not worth a B, the custom is to offer the Experts an opportunity to earn a better grade.

Giving a higher grade has no impact on your Available Points.
0
 
LVL 15

Author Comment

by:gplana
Comment Utility
Thank you.
I think I hve done well the rating becase it requires me to do a lttle more research. IfI had some oficial links I will put A grade.
Regards.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I give up.  You simply don't get it do you?
0
 
LVL 15

Author Comment

by:gplana
Comment Utility
Sorry, what have you give up ? I think I don't understand you.
Your comment "Do not use Auto-Shrink.  Period." is not very complete. You don't tell me why and you dont give me any link to an official site which explains what you are saying.
I feel my rate is logical. Sorry you disagree.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now