Auto Shrink in SQL 2005

Hi Experts
Few questions
1. What is auto shrink option in SQL 2005?
2. How do I disable it?
3. What is the advantage and disadvantage of auto shrink?

Thanks
yak01Asked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
1. What is auto shrink option in SQL 2005?
Automatically shrinks the database at a scheduled interval.

2. How do I disable it?
It shouldn't be enabled by default.  To disable:
Right Click the database > properties.
Choose Options
In the list, find "Auto Shrink".  Ensure it is set to false.

3. What is the advantage and disadvantage of auto shrink?
Advantage:  If you have LOW disk space, and your database should normally be small but OCCASIONALLY balloons for a short period of time, it can help recover the space.
Disadvantages: Physical File Fragmentation.  When the database is growing and shrinking, it will fragment on the physical disk.  This is in addition to index fragmentation.  What you end up with is a database that is spread all across your disk and WINDOWS can't access if efficiently.  

If you set your database size correctly, it should never need to be shrunk.  And growth can be managed by monitoring free space.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.