The next option in the Database Maintenance Wizard is to shrink a database. This is an option that I don’t recommend creating, no matter how tempting it is, without really understanding and knowing what you are doing. So, let’s talk about how I have come to that conclusion.
First, let’s talk about how a database acquires space that may be removed. There are two primary methods by which a database grows. They both occur by configuring your database to automatically grow when it runs out of space for new database objects. If you don’t configure your database to automatically grow, then when it runs out of space for any new data or log entries, the database becomes read only until additional space is added.
A database requires more space when new records are added to one or more tables, and there is not adequate room to store the new data. Another space requirement can occur on the database transaction log. If it doesn’t have room, no additional transactions can be created until a transaction log backup is executed, or the size of the transaction log file(s) is increased. The third process that acquires new database space occurs when you rebuild an index, and it is forced to use the database for the work in progress. This forcing to use the database may occur because the tempDB is not large enough to do the work, or the command has been executed with a parameter to force it to rebuild an index using the host database.
When any of these growth events occur, and your database is configured to automatically grow, there is often empty space left in the database. I had one database where 90% of the disk usage was for one table. When I would rebuild the indexes on this table, it would result in a doubling the size of the database as it acquired working space for the indexes. These are rough figures for percentage…but demonstrate how a single database could use only about half of the disk space it has acquired.
If I shrink this example database on a nightly basis, after rebuilding my indexes, my database will be smaller after the shrinking is completed. However, the next time my indexes are rebuilt, the database will re-acquire the disk space all over again. The reason we rebuild the indexes is to reduce fragmentation in the internal database storage. However, by expanding and shrinking the database, the operating system may be doing fragmentation as well. So, even though the file internal contents are fragmented, the file itself is fragmented on the windows file system.
The lesson here is that if you acquired the disk space once, unless this is an unusual event, you are probably going to require that same disk space again. You gain little by giving it back to the operating system, outside of reducing the size of a traditional non-compressed backup. The better answer is to perform a compressed backup and keep the acquired file space.
In short, there are few, if any, scenarios where you need to have an automated, scheduled database shrinking maintenance plan. I can see creating a maintenance plan for shrinking a database, but not assigning it to a schedule to execute automatically. Then when you need to run it, you can do so manually through the SQL Agent tasks.
One final note. I have read that there are times when you shrink a database it can fragment the internal organization of a database, which is what you were trying to remove by rebuilding indexes. If this is the case in your version of SQL Server, what you do by shrinking a database after rebuilding the indexes, is to fragment them all over again, negating all of the re-organization work you just finished.
In short, avoid shrinking a database unless you need it for some special case scenario.
Cheers,
Ben