July 19, 2012 at 2:45 pm
SQL ORACLE (7/19/2012)
Robert Davis (7/19/2012)
SQL ORACLE (7/19/2012)
Make a full backup for this database and then shrink the database.What difference will that make?
We need to truncate the log to release the space from the database first.
Full backups don't truncate the t-log.
July 19, 2012 at 2:52 pm
SQL ORACLE (7/19/2012)
Robert Davis (7/19/2012)
SQL ORACLE (7/19/2012)
Make a full backup for this database and then shrink the database.What difference will that make?
We need to truncate the log to release the space from the database first.
No you don't, the OP's shrinking the data file, not the log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2012 at 2:56 pm
Robert Davis (7/19/2012)
cmoy, when you're shrinking the file, it won't resize the file until it's done moving chunks around. So you're not going to see it shrinking a little at a time. You have to let it complete or manually run shrink file with the truncate only option after you stop it.
ah, not the most intuitive thing, but it works. The bad news is that after 16 hours I only got back about 90G. Oh well, looks like another 2 weeks at this rate....
July 19, 2012 at 3:00 pm
cmoy (7/19/2012)
Oh well, looks like another 2 weeks at this rate....
Not necessarily, depends how and where the free space is within the data file. The further towards the beginning of the file, the longer it'll take for the shrink process to 'bubble' it to the end of the file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2012 at 3:01 pm
Here is a dumb question, in what file group does your data reside?
July 19, 2012 at 3:04 pm
well, it's not on the default filegroup. Hmm...why do you ask and/or what info are you looking for?
July 19, 2012 at 3:06 pm
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.
July 19, 2012 at 3:14 pm
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.
July 30, 2014 at 3:33 pm
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
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply