How to disable the Shrink Database task in SQL Server 2000 and 2005

How to disable the Shrink Database task in SQL Server 2000 and 2005

Michelle Gutzait, Contributor

Routinely shrinking your SQL Server database is not always the best strategy for saving space, and when a DBA realizes that, the next step is to figure out how to kill the automatic task. SQL Server expert Michelle Gutzait discusses three common methods for shrinking the database and how to disable them.

Problem:

My database is being shrunk automatically but I don't know how to disable this. Please advise.

A note from the author:

Shrinking the database or database files on a routine basis is not always recommended. When the database file needs to grow during a transaction, shrinking the database may degrade performance. For more information, refer to my tip Shrinking your SQL Server database to death.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in October 2008

Ways to shrink database automatically:

  1. Auto shrink database option
  2. Shrink database maintenance task/plan
  3. Customized job

How to disable automatic shrink of a database

1) Auto shrink database option

Here's how to manually kill the auto shrink database option in both SQL Server 2000 and SQL Server 2005:

ALTER DATABASE dbname SET AUTO_SHRINK OFF

Another option is to use the GUI (Enterprise Manager/SQL Server Management Studio):

In SQL Server 2000, right click on the database - Properties - Options:


Figure 1: Disable the Auto Shrink option in SQL Server 2000 by leaving it empty. (Click on image for enlarged view.)

In SQL Server 2005, right click on the database - Properties - Options:


Figure 2: Disable the Auto Shrink option in SQL Server 2005 by setting it to False. (Click on image for enlarged view.)

2) Shrink database maintenance task/plan

With Enterprise Manager in SQL Server 2000, open the maintenance plan by double clicking on it:


Figure 3: Open the maintenance plan in SQL Server Enterprise Manager. (Click on image for enlarged view.)

Go to the Optimization tab and disable the shrink database option:


Figure 4: Disable the shrink database option from the Optimization tab. (Click on image for enlarged view.)

You also have the following options:

If the shrink database task is the only task in the maintenance plan, you can do any of these:
Delete the maintenance plan.
Disable the job running the maintenance plan.
Remove the schedule of the maintenance plan – this will remove the job. You do that by clicking on Schedule - Change button and then disable the Enable schedule option on the right top.

If the shrink database task is NOT the only task in the maintenance plan, you can do either of these:
Disable the option as described above.
Disable schedule (if there's no need to reorganize the indexes).

In Management Studio in SQL Server 2005, open the maintenance plan by double clicking it:


Figure 5: In SQL Server 2005 Management Studio, double click on the maintenance plan. (Click on image for enlarged view.)

Find the task responsible for shrinking the database and double click on it:


Figure 6: Find SQL Server Management Studio task responsible for shrinking the database and double click on it. (Click on image for enlarged view.)

Now you have a few options:

    1. If the shrink database task is the only task in the maintenance plan, you can do any of these:
      • Delete the maintenance plan.
      • Disable the job running the maintenance plan.
      • Remove the schedule of the maintenance plan – this removes the job.
    2. If the shrink database task is NOT the only task in the maintenance plan, you can do either of these:
      • Remove the task from the maintenance plan.
      • Leave it empty (no database selected).

3) Customized job

I've seen cases where the shrink database task is performed automatically by a customized job. You will either find a job with a name  implying its goal is to shrink the database or you'll find the text (for both, SQL Server 2000/2005):

DBCC SHRINKDATABASE

Or

DBCC SHRINKFILE

The first command shrinks the database, the second shrinks a specific file. Here are your options to kill the auto shrink of a customized job in SQL Server:

  1. Remove the step from the job (if it's not the only one).
  2. Convert the part of code that shrinks the database/file to be a comment.
  3. Disable the job (if it's only shrinking the database/file).
  4. Remove the job's schedule (if it's only shrinking the database/file).

ABOUT THE AUTHOR
Michelle Gutzait works as a senior database consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include SQL Server infrastructure design, database design, performance tuning, security, high availability, VLDBs, replication, T-SQL/packages coding, and more.

MEMBER FEEDBACK TO THIS TIP

Do you have a comment on this tip? Let us know.


Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top