[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 822
  • Last Modified:

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

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
gplana
Asked:
gplana
  • 10
  • 6
  • 3
  • +2
5 Solutions
 
Ephraim WangoyaCommented:
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
 
Chris MangusDatabase AdministratorCommented:
Using autoshrink is a very bad idea anyway.  Disable it.  For what it's worth, you should never need to shrink a database.
0
 
rashmi_vaghelaCommented:
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
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!

 
gplanaAuthor Commented:
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
 
gplanaAuthor Commented:
All parameters on our SQL-Server 2000 are the default parameers when you install it.
0
 
Anthony PerkinsCommented:
>>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
 
gplanaAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
gplanaAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
If your application is this critical then you need to buy more disk space!  Autoshrink isn't going to solve any problems.
0
 
gplanaAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
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
 
gplanaAuthor Commented:
Thank you all of you for your comments.
0
 
Anthony PerkinsCommented:
>>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
 
gplanaAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
gplanaAuthor Commented:
Could you please provide me the link to the EE Guidelines on grading you are referring to ?

Thank you.
0
 
Anthony PerkinsCommented:
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
 
gplanaAuthor Commented:
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
 
Anthony PerkinsCommented:
I give up.  You simply don't get it do you?
0
 
gplanaAuthor Commented:
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 10
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now