Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shrinking DB not working


Shrinking DB not working

Author
Message
cmoy
cmoy
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 179
well, it's not on the default filegroup. Hmm...why do you ask and/or what info are you looking for?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24198 Visits: 37961
cmoy (7/19/2012)
well, it's not on the default filegroup. Hmm...why do you ask and/or what info are you looking for?


If it isn't the primary file group you could try this with a test version of the database:

Create a new file group, move all the existing objects from the old file group to the new file group, then drop the old file group.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
cmoy
cmoy
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 179
Hi Lynn - Unfortunately, on my production server I have a situation where I only have 500G remaining to begin with, so its a chicken and egg thing - I would like to get back the space, but I need space to begin with.
AZJim
AZJim
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 293
I have a similar problem. Our largest database recently went on a diet. I had been urging the business to archive the really old data in the biggest tables. I got the permission and created an archive version of database with the key tables that were going to be archived. All that went smoothly. But we ran into space issues on the drive because the primary database didn't release any space.

I attempted to run a ShrinkFile Truncate Only but that didn't reclaim any space (but it did run really quick!). As I understand it (this is from my earlier days as a mainframe DBA), you can think of your .mdf file as a big bucket filled with odd sized objects. If your bucket's "high used mark" is near the top (i.e., the end of the file in SQL Server), releasing unused space won't do very much. But if you remove a lot of these odd sized objects and the high used mark is much lower, you can lower the size of your bucket (i.e, reclaim space on your .mdf file). For me, I would have to try something different from the Trucate Only option because there was data pages toward the end of the file.

I then attempted the ShrinkFile like you did and after two days I killed it. I do believe it was legitimately defragging the file so it could be shrunk, but as another responder on this thread mentioned, there is a lot of overhead when you are defragging a data file block-by-block. Sometimes it has to move another block to get target block into the slot. I did find better results when I dropped down to 2-3 GB reductions for my ShrinkFile. This took about 45 minutes per GB. So if I wanted to proceed until I reclaimed all unused space, I would be screwing with this thing for days.

So you can have an enormous amount of work being done and not seeing very much results. So much, there almost doesn't seem to be enough reason to do it. This is especially true when you find out that all the reorganizing of the pages is actually ***causing new fragmentation***. You see, you might have a lot of wasted space, but the data can actually be fairly sequentially laid out. Now the ShrinkFile has taken pages at the logical and physical end of the sequence and moved them to open spaces in the physical beginning of the file. Your users will be pleased that you saved space, but mad as hell that you messed with the performance. You should run an index rebuild afterwards to fix this problem. But unless your SQL Server edition is Enterprise, an index rebuild has to be done with the index offline. So in order to regain the performance you lost, you will have a period of badly performing queries if they are attempting to use the indexes that are currently being rebuilt.

So you really have to ask yourself whether it is worth it. Are you doing it just to save a little space (even two TB today isn't all that expensive)? Then let your manager know what the cost will be in the form of your time and downtime. You might find that it is better to keep things as is. Also, keep in mind that if you data will **ever** grow back that big, you will avoid the overhead of formatting the new data extents on the file.

Personally, I think this might be an opportunity for the third party tools vendors to come along and create a better utility to accomplish this since Microsoft probably will never do it.

AZJim
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search