August 3, 2011 at 10:28 am
What’s the fastest method in SQL Server 2005 to shrink the available space in the data file to as small an amount as possible? This is for a test or development copy of our DB which does not need anywhere near the amount of growth in the data file that our live DB does.
I know how to shrink the data file I just want to find out what is the fastest way to do it since it takes a while using the SSMS Shrink File procedure and if there’s a faster way to do it that would be great.
Thanks
Kindest Regards,
Just say No to Facebook!August 3, 2011 at 2:12 pm
We try to avoid shrinking files because of the index fragmentation that can result. Recently, we were faced with a situation where we really needed to shrink a set of files by over 100 GB each. We batched the shrink process in 1 GB increments and that worked well. We then had to rebuild the indexes.
August 3, 2011 at 3:14 pm
Shrinkfile/shrinkdatabase is the only way.
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
August 3, 2011 at 3:56 pm
Just curious - how often are you going to be refreshing this environment? If it is going to be a regular scheduled activity I wouldn't bother with shrinking. You will need that space for the next refresh - and shrinking the files will allow that space to be used by other databases or files.
Then, when you go to refresh again - you might not have enough space available to restore the database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 3, 2011 at 4:00 pm
The frequency will vary but it will be at least once a week I'd say but possibly more. Its used as our testing DB , something the developers, Training people and anyone else who needs to, to try stuff like fixes and so on.
Thanks
Kindest Regards,
Just say No to Facebook!August 3, 2011 at 4:02 pm
Gail,
i was thought so but hoped otehrwise. Thanks
BTW - I've always wanted to ask if your avatar is one of the chaarcters from the short lived Babylon 5 spin off? I forget the show name but I know it was a spin off from the awesome BABYLON 5 show.
Kindest Regards,
Just say No to Facebook!August 3, 2011 at 4:17 pm
YSLGuru (8/3/2011)
The frequency will vary but it will be at least once a week I'd say but possibly more. Its used as our testing DB , something the developers, Training people and anyone else who needs to, to try stuff like fixes and so on.Thanks
Then I wouldn't bother with performing a shrink at all. The overhead of performing the shrink and rebuilding the indexes (which just might grow it back) would just be a waste of time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 4, 2011 at 2:24 am
YSLGuru (8/3/2011)
BTW - I've always wanted to ask if your avatar is one of the chaarcters from the short lived Babylon 5 spin off? I forget the show name but I know it was a spin off from the awesome BABYLON 5 show.
Crusade.
No, it's from the B5 show itself. That's a (rather bad) shot of the Ranger Marcus Cole.
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
September 8, 2011 at 5:04 pm
GilaMonster (8/4/2011)
YSLGuru (8/3/2011)
BTW - I've always wanted to ask if your avatar is one of the chaarcters from the short lived Babylon 5 spin off? I forget the show name but I know it was a spin off from the awesome BABYLON 5 show.Crusade.
No, it's from the B5 show itself. That's a (rather bad) shot of the Ranger Marcus Cole.
Gail - I am assimuing you are a B5 fan and so this might be of interets to you. Its a year old buit still. The below link is to a movie/tv website that discusses MJS and nhow the Budget necessary for a big screen B5 might not be outside the realm of possability. http://screenrant.com/babylon-5-movie-brusimm-5178/
Kindest Regards,
Just say No to Facebook!Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply