shrink/resize database
-
Hi All,
We have had a MSSQL database for data warehouse of size 1.5TB and recently with the purging and arching we were able to cleanup some data and now we have 900GB for free space in data files. We have two different data file sitting in different LUNs which are made from same disk array of EMC storage.
Our goal is to shrink/resize database without hitting any snag with performance in most efficient manner. Should we shrink the database or data files or create new data files with smaller size and move objects to new files and drop bigger files when empty.
If we use rebuilding clustered indexes to move objects between data file what is impact where there is 100GB clustered index ?
Your valuable opinion will be much appreciated!
-
Shrink is not recommended. Paul Randall has a great blog post regarding this.
http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
The method I like to recommend is as follows:Create a new filegroupMove all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same timeDrop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)Basically you need to provision some more space before you can shrink the old files, but it’s a much cleaner mechanism.
-
deep.tkt (1/24/2013)
If we use rebuilding clustered indexes to move objects between data file what is impact where there is 100GB clustered index ?there are couple of things yoou need to keep in mind like
FILLFACTOR,ONLINE,SORT_IN_TEMPDB,STATISTICS_NORECOMPUTE,MAXDOP.
Sufficient space should be there and do this operation in off-business hours
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
usefull link http://msdn.microsoft.com/en-us/library/ms184246(v=sql.90).aspx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
usefull link http://msdn.microsoft.com/en-us/library/ms184246(v=sql.90).aspx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply