October 18, 2010 at 9:33 am
I don't want to shrink my database but will SSMS 2008 automatically return free disk space to my operating system? Is there a way to manually do it without shrinking the database?
I have about 250g of free space inside my database that I need to have returned to me.
Don
October 18, 2010 at 10:32 am
You'll have to shrink the databases to get that file space back. Watch out for both file and data fragmentation when you do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 18, 2010 at 10:57 am
I was afraid that would be the answer. 🙁
Don
October 18, 2010 at 11:31 am
What if I shrink the individual data files? Is that better or worse than shrinking the database and will that give me back my free space?
Don
October 18, 2010 at 12:05 pm
It'll have the same results.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 18, 2010 at 2:32 pm
which file occupies the most space and has the most free space?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 18, 2010 at 2:44 pm
The data file (there's only one) is 490 gigs and has 250 gigs free inside the file.
Don
October 18, 2010 at 3:35 pm
so what produced this mammoth amount of fle growth and subsequent free space in the first place? Shrinking blindly without knowing the initial cause, it could just grow again!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 18, 2010 at 3:53 pm
The growth is due to a miscalculation on the part of "Business" to the amount of transactions they were going to see and the space is being created by the removal of data to either archive or the ether if it wasn't needed in the first place.
Don
October 18, 2010 at 4:11 pm
OK, it's important to know this to keep a handle on the growth!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2010 at 9:01 am
Absolutely, which is why they hired me. They didn't have a dba when business built and implemented their database design.
Don
October 19, 2010 at 9:58 am
If you decide you need to shrink the data files, you might find the script on the following link useful:
Shrink DB File by Increment to Target Free Space
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355
As has been mentioned, you will need to rebuild your indexes or defrag and update statistics when your are done.
If you decide to rebuild your indexes, you should leave enough space after the shrink for the largest table in the database because reindexing creates a new copy of the table and it will just grow again if there is not enough space.
October 19, 2010 at 10:50 am
Michael,
Does shrinking the database in small increments have a greater or lesser fragmentation effect on the indexes? Or does it really make no difference?
Don
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply