question

sqlmunkee avatar image
sqlmunkee asked

Is DBCC SHRINKFILE allowed during a database backup operation ?

OK, so don't shout at me for using DBCC SHRINKFILE, but I need to reclaim free space from partition filegroups after switching out a partition and archiving the data somewhere else. I've been testing an approach which works fine. It takes the last partition that was switched out (and therefore is now empty) and shrinks the data files in the partition file group. Problem is, when I moved this routine to production, the DBCC SHRINKFILE statements didn't work. The only difference between test and production is that a full database backup is running at the same time in production. Any help / pointers greatly appreciated.
backupdbcc
1 comment
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.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Change your schedule so that the two jobs aren't running at the same time?
0 Likes 0 ·

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
Thinking out loud here... * A full database backup is a page by page copy of the database. * A Shrinkfile operation is going to be throwing data and pages around, and throwing pages away. I guess that they're not allowed to happen simultaneously because of this...
1 comment
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.

sqlmunkee avatar image sqlmunkee commented ·
That was kind of my thinking as well. Thanks for the reply.
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.