I just wanted some opinions before I went and did this on my production DB machine. We have a 240GB database with multiple web services connecting to it and lots of read/write activity.
What I wanted to do was shrink the DB file and Optionally, select the Release unused space check box.
On BOL I found this: Selecting this option causes any unused space in the file to be released to the operating system and shrinks the file to the last allocated extent. This reduces the file size without moving any data.
However, would this cause contention, blocking, locks, slow-down of server? Also, would we lose data, etc? Or is this a no-harm procedure. I have autogrowth set to grow my DB 1gig everytime it hits the allocated DB size.Thanks,
asked Apr 14, 2011 at 08:04 AM in Default
Shrinking and growing causes O/S level fragmentation. Best Practice is to set the file to its expected size initially.
[Paul Randal] covers it pretty clearly.: http://www.sqlskills.com/BLOGS/PAUL/post/Auto-shrink-e28093-turn-it-OFF!.aspx
answered Apr 14, 2011 at 08:16 AM