June 7, 2011 at 1:15 pm
I've got a database which has a hundred tables or so, but where a single table is responsible for 90% of the disk space in the database data file. I sometimes need to restore a copy of this database just to get at some records in one of the smaller tables.
To make the restore process faster and require less disk space I decided to create another filegroup, move the large table to it, and then shrink the data file in the primary filegroup, hopefully reducing it by 90%. The idea is that I can then restore just the primary filegroup to a new database, which should go much quicker.
All goes well until the shrink file operation which runs for hours. This when I'm shrinking just a 25 gig data file! I hope to do the same process on another database which is 320 gig, but that looks impractical at this point.
Is there any trick to reducing the size of that data file containing the primary file group quicker? It's 90% empty space. BTW, the database is is simple recovery mode.
June 7, 2011 at 2:28 pm
It's much easier to add space than to shrink a data file.
Only thing I can recommend is move all your objects into the second filegroup and then shrink the empty data file and then move the smaller tables back in.
Shrinking a data file can take hours on end because it is not an easy process and results in badly fragmented tables and indexes.
Even the developer of the Shrink process Paul Randal recommends people not use the Shrink command if possible.
-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer
June 7, 2011 at 4:46 pm
I would move the other tables.
Why move a table that takes 90% of the space while you can move the ones that take 10% ?
If the answer is "because there are 200 hundred other tables" I would say that that's not such a big problem, you can always write a simple script that would create the script you need to move all those tables.
This will be faster and no shrinking would be needed.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 7, 2011 at 5:39 pm
He mentioned that he sometimes has to do restores to get data from one of those 200 tables.
You cannot restore a specific filegroup without first restoring the primary file group.
I would think that the current filegroup is the primary one so you would have to move the larger table off in order to take advantage of filegroup restore.
-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer
June 8, 2011 at 8:10 am
Exactly. The tables were all in the primary group; I moved the large one out so I could do a restore of just the other tables. I believe this separation is also beneficial in the case of data corruption in the large table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply