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:
- Auto shrink database option
- Shrink database maintenance task/plan
- 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:
- 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.
- 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 ((Content component not found.)) 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:
- Remove the step from the job (if it's not the only one).
- Convert the part of code that shrinks the database/file to be a comment.
- Disable the job (if it's only shrinking the database/file).
- Remove the job's schedule (if it's only shrinking the database/file).
| ABOUT THE AUTHOR: |
|
Michelle Gutzait works as a team member of the SQL Server Database Experts at www.pythian.com, a worldwide company providing Remote Administration services for databases, operating systems, SANs and networks. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include SQL infrastructure and database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS/SSIS packages, administrative and infrastructure tools development, reporting services and more.
Copyright 2008 TechTarget
|
|
MEMBER FEEDBACK TO THIS TIP
Do you have a comment on this tip? Let us know.
