Regarding database file shrinking

Question

Regarding database file shrinking

aks_osd

Old Hand

Points: 337
More actions
July 10, 2008 at 2:39 pm

#154517

Hi
MY db is around 150 GB and i have 11 data files with more different capacities. My DB is in simple recovery mode.
I just need to confirm if i go to shrink file option in the enterprise manger and should i truncate the free space from the file , because shriking the database is not freeing up any space.
Can anyone please suggest.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

JeremyE SSCoach Points: 16200 More actions · Answer 1

Shrinking a database is generally a bad idea unless there is a compelling reason to do so. Are you running out of disk space? Why are you looking to shrink the database?

Jayakumar Krishnan SSCarpal Tunnel Points: 4710 More actions · Answer 2

Yes, as above said, we can try to avoid the shrink option, unless you really needed to free up some space to OS. Have a look at the below link you'll get an idea.

http://www.sqlservercentral.com/Forums/Topic489349-357-1.aspx#bm489445

Thanks
Jay
http://www.sqldbops.com

aks_osd Old Hand Points: 337 More actions · Answer 3

Select Database right click-> Tasks -> Shrink database -> click OK

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

what are you shrinking data files or log files

SQL server will not shrink past an active portion of the file, if that happens to be near the end of the physical file then it will never shrink. Again why do you want to shrink the file(s)? Is it because they are consuming more space than you planned for?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Don't shrink your database. It causes massive index fragmentation and the DB will quite likely just grow again, possibly causing file-level fragmentation.

See - shrinking databases[/url]

Why do you want to shrink?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

this shrinking malarky has been done to death all over the forum:D

good job cos i learnt something new 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Perry Whittle (7/14/2008)
this shrinking malarky has been done to death all over the forum:D

I know. That's why I wrote the blog post in the first place, so I didn't have to keep on explaining why shrinking is bad.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

GilaMonster (7/14/2008)
I know. That's why I wrote the blog post in the first place, so I didn't have to keep on explaining why shrinking is bad.

😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉