Shrinking a database

I used the following scripts to shrink down a database.

1 create a new filegroup + files
2. move all heaps to the new filegroup
3. move all indexes to the new filegroup
4. Get the files for the old filegroup right
5. Move the tables back and drop any clustered indexes created in (2)
6. Remove the new files and filegroup

--this will create a clustered index on heaps on the new filegroup, 167 refers to varchar 8000, which is none indexable

SELECT 'create clustered index jlc on ' + d.name + '.' + c.name+ '('+ QUOTENAME(e.name) + ') on [secondary]',* FROM sys.indexes a
INNER JOIN sys.data_spaces b ON b.data_space_id = a.data_space_id
INNER JOIN sys.objects c ON c.object_id = a.object_id
INNER JOIN sys.schemas d ON d.schema_id = c.schema_id
INNER JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY object_id ORDER BY column_id) r FROM sys.columns WHERE system_type_id != 167 AND user_type_id !=167) e ON e.object_id = a.object_id AND e.r = 1
WHERE b.name = 'Primary' AND c.type = 'U' AND a.type_desc = 'HEAP'

--this will drop the above indexes

SELECT 'DROP INDEX jlc ON ' + c.name + '.' + b.name FROM sys.indexes a
INNER JOIN sys.objects b ON b.object_id = a.object_id
INNER JOIN sys.schemas c ON c.schema_id = b.schema_id
WHERE a.name = 'jlc'



--change the filegroup name in two separate places
--this will move indexes to the new filegroup 
--found on google, then modified

SELECT ' CREATE ' +
 CASE 
 WHEN I.is_unique = 1 THEN ' UNIQUE '
 ELSE ''
 END +
 I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
 QUOTENAME(I.name) + ' ON ' +
 SCHEMA_NAME(T.schema_id) + '.' + QUOTENAME(T.name) + ' ( ' +
 KeyColumns + ' ) ' +
 ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
 ISNULL(' WHERE ' + I.filter_definition, '') + ' WITH ( ' +
 CASE 
 WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
 ELSE ' PAD_INDEX = OFF '
 END + ',' +
 'FILLFACTOR = ' + CONVERT(
 CHAR(5),
 CASE 
 WHEN I.fill_factor = 0 THEN 100
 ELSE I.fill_factor
 END
 ) + ',' +
 -- default value 
 'SORT_IN_TEMPDB = OFF ' + ',' +
 CASE 
 WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
 ELSE ' IGNORE_DUP_KEY = OFF '
 END + ',' +
 CASE 
 WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
 ELSE ' STATISTICS_NORECOMPUTE = ON '
 END + ',' +
 ' ONLINE = OFF ' + ',' +
 CASE 
 WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
 ELSE ' ALLOW_ROW_LOCKS = OFF '
 END + ',' +
 CASE 
 WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
 ELSE ' ALLOW_PAGE_LOCKS = OFF '
 END + ',DROP_EXISTING = ON ) ON [' +
 'PRIMARY' + ' ] ' + CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM sys.indexes I
 JOIN sys.tables T
 ON T.object_id = I.object_id
 JOIN sys.sysindexes SI
 ON I.object_id = SI.id
 AND I.index_id = SI.indid
 JOIN (
 SELECT *
 FROM (
 SELECT IC2.object_id,
 IC2.index_id,
 STUFF(
 (
 SELECT ' , ' + QUOTENAME(C.name) + CASE 
 WHEN MAX(CONVERT(INT, IC1.is_descending_key)) 
 = 1 THEN 
 ' DESC '
 ELSE 
 ' ASC '
 END
 FROM sys.index_columns IC1
 JOIN sys.columns C
 ON C.object_id = IC1.object_id
 AND C.column_id = IC1.column_id
 AND IC1.is_included_column = 
 0
 WHERE IC1.object_id = IC2.object_id
 AND IC1.index_id = IC2.index_id
 GROUP BY
 IC1.object_id,
 C.name,
 index_id
 ORDER BY
 MAX(IC1.key_ordinal) 
 FOR XML PATH('')
 ),
 1,
 2,
 ''
 ) KeyColumns
 FROM sys.index_columns IC2 
 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
 GROUP BY
 IC2.object_id,
 IC2.index_id
 ) tmp3
 )tmp4
 ON I.object_id = tmp4.object_id
 AND I.Index_id = tmp4.index_id
 JOIN sys.stats ST
 ON ST.object_id = I.object_id
 AND ST.stats_id = I.index_id
 JOIN sys.data_spaces DS
 ON I.data_space_id = DS.data_space_id
 JOIN sys.filegroups FG
 ON I.data_space_id = FG.data_space_id
 LEFT JOIN (
 SELECT *
 FROM (
 SELECT IC2.object_id,
 IC2.index_id,
 STUFF(
 (
 SELECT ' , ' + C.name
 FROM sys.index_columns IC1
 JOIN sys.columns C
 ON C.object_id = IC1.object_id
 AND C.column_id = IC1.column_id
 AND IC1.is_included_column = 
 1
 WHERE IC1.object_id = IC2.object_id
 AND IC1.index_id = IC2.index_id
 GROUP BY
 IC1.object_id,
 C.name,
 index_id 
 FOR XML PATH('')
 ),
 1,
 2,
 ''
 ) IncludedColumns
 FROM sys.index_columns IC2 
 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
 GROUP BY
 IC2.object_id,
 IC2.index_id
 ) tmp1
 WHERE IncludedColumns IS NOT NULL
 ) tmp2
 ON tmp2.object_id = I.object_id
 AND tmp2.index_id = I.index_id
WHERE ds.name = 'Secondary'
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: