Problem solve Get help with specific problems with your technologies, process and projects.

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

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.

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.

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:

Disable the Auto Shrink option in SQL Server 2000 by leaving it empty
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:

Disable the Auto Shrink option in SQL Server 2005 by setting it to False
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:

Open the maintenance plan in SQL Server Enterprise Manager
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:

Disable the shrink database option from the Optimization tab
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:

In Management Studio in SQL Server 2005, double click on the maintenance plan.
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:

Find SQL Server Management Studio 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.


This was last published in October 2008

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close