Shrink Database File


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.


more ▼

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

avatar image

1.3k 75 104 147

(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 covers it pretty clearly.

more ▼

answered Apr 14, 2011 at 08:16 AM

avatar image

12.1k 30 36 42

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Apr 14, 2011 at 08:04 AM

Seen: 1014 times

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

Copyright 2018 Redgate Software. Privacy Policy