4

We have a legacy SQL Server 2005 DB that is 8 TB in size. We will be purging data older than 5 years from this DB. After the purge process is complete, I want to be able to shrink the DB or reduce the file size of the DB.

I know shrinking (DBCC shrink..) is not a good option. Hence, was thinking of exporting all tables/objects/data via a SSIS package to a new DB so I can reclaim all the space. Is there any other way of reducing the size of mdf and ndf files?

Alternatively, I could try exporting all data to a new filegroup and deleting the original filegroup. However, most of the tables are heaps and don't have a clustered index. Is it possible to move all data/tables to another filegroup without a clustered index? Also, can I delete the primary filegroup if I move all the data to a different filegroup?

Improve this question
5
  • 3
    Do you anticipate it growing again? The knock against shrinking database files is that your data is that if your data demonstrate a requirement for that size, you don't want to pay the price of expanding files again when your data grows (even with Instant File Initialization enabled). You'll want to size the data (and log) files correctly, or as close to correctly as possible. If you consider what you're doing an exercise to getting to that point, then shrink away, and then resize properly. – swasheck Jan 6 '17 at 22:02
  • Yes, the database will grow in size again but we will be enabling purge jobs that will purge data older than a year regularly so DB size will be in check after the first initial purge has been completed. I tried shrinking the mdf and ldf files in UAT and the process kept running for 10 days, after which I killed and rollback took another 10 days. Hence, I am not confident about doing the same on PROD. This is our PROD DB. – Nancy Jan 6 '17 at 22:40
  • So, after purging the data, you create a new filegroup in its own .ndf file, move tables and indexes to the new filegroup (Use the Clustered Index (CI) migration method by either adding a CI on the table on unique data, or adding an identity column and making it the table's CI, for all tables without CIs). Once everything is migrated, shrink the .mdf file (which should go MUCH faster without all that data to move around), then migrate things back and drop the new .ndf file and filegroup (or keep it and use it to split things over more drives - up to you). That sounds like a plan. – Laughing Vergil Jan 6 '17 at 22:46
  • You can either keep the new Clustered indexes (which is not a bad thing for data defrag purposes) or drop them and go back to heaps of heaps. I recommend clustered indexes because my DBs run much faster when they get larger if I can defrag the data as well as the indexes. – Laughing Vergil Jan 6 '17 at 22:49
  • 1
    @Nancy I tend to .mdf shrinks (when appropriate), in much smaller bites, typically in a loop. Whatever size works for you...100MB, 10 GB, etc. All at once sucks :) – Kevin3NF Jan 7 '17 at 4:07
2

Go with your alternate option - create a new file group, move the data there, and then drop the old file group. That's Paul Randall's advice, even:

So what if you do need to run a shrink? For instance, if you’ve deleted a large proportion of a very large database and the database isn’t likely to grow, or you need to empty a file before removing it?

The method I like to recommend is as follows:

Create a new filegroup. Move 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 time. Drop 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.

If you absolutely have no choice and have to run a data file shrink operation, be aware that you’re going to cause index fragmentation and you should take steps to remove it afterwards if it’s going to cause performance problems. The only way to remove index fragmentation without causing data file growth again is to use DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE. These commands only require a single 8KB page of extra space, instead of needing to build a whole new index in the case of an index rebuild operation.

Bottom line – try to avoid running data file shrink at all costs!

Improve this answer
0

Shrinking isn't good because it fragments your indexes, if most of your tables are HEAPS then it sounds like SHRINK is a viable option. The biggest headache from Shrink is what it does to indexes.

Shrinking doesn't have to be done at once, do it in stages, reducing by manageable chunks.

Perform a review of data-types. If you can change columns from BIGINT to INT during your transition then you will save additional space. Reducing the footprint of columns will make index repair less cumbersome.

Perform monitoring of index usage with sys.dm_db_index_usage_stats, indexes without scans, seeks or lookups should be removed.(note: these numbers get reset when the server is restarted)Remember a scan of a clustered index is just the same as a table scan. Removing indexes makes shrinking less of a hassle.

Improve this answer
4
  • OK but these points do not really answer the question. – ypercubeᵀᴹ Jan 7 '17 at 17:12
  • Thank you all for your suggestions. I am trying these options on the DEV server now – Nancy Jan 13 '17 at 23:21
  • I will try to move everything to a new filegroup and then shrink the Primary filegroup. Let's assume, I am left with about 7 TB available space in my primary filegroup after moving objects to other filegroups. Do you think it will take more than 24 hours to shrink the Primary FileGroup with 8 TB in size but available space of 7 TB? – Nancy Jan 13 '17 at 23:31
  • It's all down to your san's io performance. Try shrinking by 10 percent and seeing how you get on. Each consecutive shrink will get slower but they can be done out of core business hours until completion. – pacreely Jan 13 '17 at 23:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.