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:
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:
[IMAGE]
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:
[IMAGE]
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:
[IMAGE]
Figure 3: Open the maintenance plan in SQL Server Enterprise Manager. (Click on image for enlarged view.)
Go to the Optimization tab and disa
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
ble the shrink database option:
[IMAGE]
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:
[IMAGE]
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:
[IMAGE]
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 NOT the only task in the maintenance plan, you can do either of these:
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:
[TABLE]
MEMBER FEEDBACK TO THIS TIP
Do you have a comment on this tip? Let us know.
