[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2010-09-02
21
Medium Priority
?
814 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 3
  • +2
21 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 300 total points
ID: 33592169
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 600 total points
ID: 33592853
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 300 total points
ID: 33593793
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 15

Author Comment

by:gplana
ID: 33613208
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
ID: 33613211
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 300 total points
ID: 33613879
>>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
ID: 33615252
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
ID: 33618045
>>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
ID: 33618135
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
ID: 33620356
If your application is this critical then you need to buy more disk space!  Autoshrink isn't going to solve any problems.
0
 
LVL 15

Author Comment

by:gplana
ID: 33621159
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 600 total points
ID: 33621614
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
ID: 33621871
Thank you all of you for your comments.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33622591
>>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
ID: 33624271
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
ID: 33632894
>>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
ID: 33634022
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
ID: 33642047
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
ID: 33643879
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
ID: 33645822
I give up.  You simply don't get it do you?
0
 
LVL 15

Author Comment

by:gplana
ID: 33646016
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

656 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