x

Shrink Database File

Hi,

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,
Slick
more ▼

asked Apr 14, 2011 at 08:04 AM in Default

Slick84 gravatar image

Slick84
1.3k 75 102 142

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Shrinking and growing causes O/S level fragmentation. Best Practice is to set the file to its expected size initially.

[Paul Randal][1] covers it pretty clearly.

[1]: http://www.sqlskills.com/BLOGS/PAUL/post/Auto-shrink-e28093-turn-it-OFF!.aspx
more ▼

answered Apr 14, 2011 at 08:16 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

Great article Thanks. One question arises though. What if I have my clustered indexes on a seperate file group other than my Primary filegroup. In that case, I wouldnt face this extreme fragmentation..right? or am I understanding this thing wrong and that maybe if you shrink the database file, you automatically shrink all filegroups as well?
Apr 14, 2011 at 08:25 AM Slick84
Basically any shrinking/growing puts potential fragmentation into play. If you Shrink dB all files are affected. If you use shrink file then only the targeted file is shrunk.
Apr 14, 2011 at 11:08 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x126
x37

asked: Apr 14, 2011 at 08:04 AM

Seen: 831 times

Last Updated: Apr 14, 2011 at 08:04 AM