Scared of ShrinkDB so will this alternative be OK?

Question

I've read too many horror stories to even touch any T-SQL command with SHRINK in it, but I need to sort out a number of my SQL Server 2008 databases that have bloat.

My databases consist of a simple set of tables, which have primary keys and standard indexes added onto these tables.

With the above in mind, I've created the following:

DECLARE @sourcedb NVARCHAR(MAX) = N'DB100';
DECLARE @destdb NVARCHAR(MAX) = N'DB200';
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'SELECT * INTO ' + @destdb + '.dbo.' + name + 
               ' FROM ' + @sourcedb + '.dbo.' + name + ';' FROM sys.tables;
EXEC sp_executesql @sql;
GO

use DB200

-- Re-add Primary Keys
ALTER TABLE table1 ADD PRIMARY KEY (TRANSID);
ALTER TABLE table2 ADD PRIMARY KEY (ADMINID);
etc etc

-- Re-Add Indexes
CREATE INDEX [TRANSID] ON [table1] ([TRANSID])
GO

CREATE INDEX [PERSONID] ON [table1] ([PERSONID])
GO

CREATE INDEX [AGEID] ON [table1] ([AGEID])
GO

etc etc

My plan was to then backup the new database and restore it over the old one in the twilight hours.

Early test shows that a 365MB database goes down to 147MB and everything seems to be OK.

Can you see any pitfalls in my plan?

There is potential to delete a lot of rows from my databases over time, as the user can remove redundant information in a few clicks, so I'm assuming that the select/insert option is only copying over the rows that are currently in each table and therefore the bloat of days past is left by the wayside.

Answer 1

it's not a good idea to shrink your database on a regular basis.

It can make lots of sense to shrink your database after a large amount of data has been removed permanently.

There are two main reasons not to shrink your database:

1. Fragmentation: When your tell SQL Server to shrink a data file, it has to free up space at the end of the file. It does so by moving whatever pages are at the end of the file to ask close to the start of the file as possible. This causes your indexes and tables to get fragmented.

   De-fragmenting your indexes/tables requires a certain amount of free space. And, de-fragmenting can release space as well. So, after defragmenting your DB, you are likely to have the most free space you ever see. Which makes it tempting to shrink the database. Which makes it so you need to de-fragment it. you see the vicious circle here.

2. Cost of re-growing: The other issue is that, if your database has grown to its current size, then at some point all the space was needed. It may not be needed on a daily basis, but it was needed at some point. You may have a process that requires extra space (like for instance, de-fragmentation, as noted above; or, a nightly data load into a staging table for processing).

   If you shrink your data file smaller than what it needs for normal maintenance, then it will need to regrow. If you have instant file instantiation turned on, this isn't as expensive as it would otherwise be. However, that doesn't help your log file; if you shrink that, it has to zero out the new file space when it has to grow.

   You can be manually juggling disk space in such a way that, if all your files were the size they need to be for a week, you'd be out of disk space. This can be a valid temporary solution (if one with some performance hits). However, if any of this is being done manually? You better never go on vacation, because things will blow up before you're back.

Now, as noted, there are good reason (and good ways) to shrink your database. If you have greatly reduced the amount of data in the database, and it won't be growing back for a long time, then releasing space for other use makes perfectly good sense.

For what you're talking about, it's possible that a shrink and a defragmentation may be faster than copying everything to a new database. It's almost certainly safer; for your plan, you have to make sure that everything in place in the original DB is in place in the new DB. Copying over tables that include foreign keys means you have to be sure you copy the data over in the right order.

A few tips:

• If you know something major is about to happen (loading millions of rows for a new marketing campaign, for instance), don't bother shrinking now.
• Never use DBCC SHRINKDATABASE; SQL Server decides which files to shrink, whether data, log, or other. You want to be in control of what space is freed. Use DBCC SHRINKFILE.
• While you should definitely do a test run before doing this in production, realize that (unless your test DB is a restore of your production DB) how things go in test won't necessarily reflect how things go in production.
• While I think this is safer than your new plan, it's not a bad idea to take a backup before you start, to be safe.
• DBCC SHRINKFILE might not block production activity. Then again, it might. It's safest to plan this during a planned outage.
• Plan on defragmenting your database as a part of that outage, after your shrink finishes. The DB may grow some during this; that's fine, and the extra space should be left in place.

Answer 2

So my hunch for the main reason why space usage is going down is due to index/table fragmentation. Query sys.dm_db_index_physical_stats on the old database then compare to same query on new database. Check col avg_page_space_used_in_percent (use mode=detailed)

Instead, perhaps use ALTER INDEX ... REBUILD/REORGANIZE regularly to maintain indexes in place. This approach should also be faster than if you copied and recreated the indexes. You're freeing space after future deletes as well.

Hope that's helpful.