One Size Fits All…Not
As a result of the different views on database shrinking are expressed it becomes clear that there is no single technique that fits all cases. For example, yesterday I proposed that disk space is cheap, and that disk space acquired by re-indexing, etc. would most likely be needed in the near future, and that nothing is really gained by shrinking. Today Edward shares an experience where this concept does not fit well at all…
Disk space is Cheap? In whose world? The Navy Data center which host our databases charge by the mb. With people being furloughed one day a week without pay for the rest of the fiscal year due to sequester, paying extra money for an un-shrunk database does not make sense. Several databases grow to a highly swollen size due to temporary periodically transferred data, which is deleted as soon as the process is completed. Then our agency ends up paying for empty space while people are having their pay cut. Sure that makes good sense.
Often there are costs outside our control. And in this case it is clear there is value in shrinking. Most likely a database with this kind of activity (temporary transferred data) is not going to fall under a normative process.
I wonder if we could save even more money by using a partitioned table in a database where data is transitory, since the partition containing the temporary table may simply be dropped. The entire partitioned file group removed from the database altogether, after the table is truncated.
This process would be incredibly faster than shrinking the database, and allow funds to be used for paying salary instead of buying more expensive hardware. Shrinking a database is a slow, intensive process.
Thanks, Edward, for keeping the conversation honest. There are very few practices that work in all situations. Do you have a scenario where database shrinking is mandatory, or disallowed? Share your thoughts or experiences by leaving a comment below, or dropping me an email at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008/2012
Written by Townsend Security
Simplify encryption and key management on … (read more)
Featured Script
GetRowCountAllTables.sql
Returns the rowcount for all tables in the database. (requires fn_getrowcount.sql)… (read more)