question

Slick84 avatar image
Slick84 asked

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
administrationshrink-database
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Blackhawk-17 avatar image
Blackhawk-17 answered
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
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 avatar image Slick84 commented ·
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?
0 Likes 0 ·
Blackhawk-17 avatar image Blackhawk-17 commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.