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.