Auto Shrink in SQL 2005

Posted on 2009-02-16
Last Modified: 2012-05-06
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?

Question by:yak01
    1 Comment
    LVL 39

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now