Hello Geeks,
Welcome to the new series of seven wonders of SQL Server. These are not the wonders which make you say Aww. These are the ones which make you really wonder why. I will be covering the seven of such jaw dropping situations where you wonder what is happening and few bizarre reasons why it is the way it is.
Last week we have seen how important it is to control administrator permissions on your SQL Server. Today I will take you through another feature which I would be happy if Microsoft removes it.
Let us talk about data file shrink. If you are not aware of the bad things a shrikfile or shrinkdatabase does to your data you should read this post from Paul Randal. And it is a misconception that data file shrink file does not affect performance.
The big devil in disguise is the Auto Shrink option for a database. I have seen many vendor databases which use staging tables. They setup the database auto shrink option to true. With the storage costs plummeting down do you really need to shrink your data file temporarily every day/night? If the data in your database is tend to grow, why do you need to shrink and regrow your file every time? And the most bizarre reason I have heard is to keep the database size under control. HA HA HA…
If you haven’t been wondering yet, let me give you one more reason. Imagine an intelligent database professional (I would rather not call him that) set up maintenance tasks to rebuild indexes followed by database shrink. For obvious reasons shrinking a data file is bad. Unless needed in specific cases where you had done a data cleanup and your database is not going to grow in near future, it doesn’t make sense to shrink the data file. If you are still not convinced with my suggestion, try shrinking a data file and check the index fragmentation. Or you can read the blog post above from Paul Randal, if you haven’t already.
First things first, use this script to find any databases which have the killing auto shrink option on.
SELECT name, is_auto_shrink_on FROM sys.databases WHERE is_auto_shrink_on = 1
Disable the auto shrink option and you have done a good job.
ALTER DATABASE <database_name> SET AUTO_SHRINK OFF
Write a mail to your manager about what you have done. Explain how you saved your environment from unnecessary performance problems created due to bad configuration of database settings.
Now, let us talk about the scenario where you have to shrink the data file. Remember the following points to perform a shrink.
- When you have to shrink a database, always use DBCC SHRINKFILE over SHRINKDATABASE. SHRINKFILE has more options and more granular. You can specify target file size or amount of free space to be left after shrink.
- Plan to perform the shrink when the load is less on the database. If all the free space cannot be reclaimed in single window, split this into multiple windows and perform the shrink in batches. For example, to shrink 200 GB, perform shrinks in 2 GB batches. Once you are close to the maintenance window, stop the shrink.
- Check the index fragmentation of tables before the shrink operation.
- After the shrink run index maintenance job to clear any fragmentation created due to shrink file operation.
All the best and share your experience in the comments.
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
