SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unable to drop a file with SHRINKFILE and EMPTYFILE option


Unable to drop a file with SHRINKFILE and EMPTYFILE option

Author
Message
Christo Steenkamp
Christo Steenkamp
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 18
We have a database that consists of 3 data files and a log file on different physical drives. Due to a change in the nature of the data, we have a large amount of free space available now and we want to drop 2 of the data files and be left with just the original data file that was created. The other files were added over time as the data grew.

The details of the file to be dropped is as follows:
Currently allocated: 127253.06 MB
Free Space : 127250.25 MB (99%)

There is more than enough space available in the other files to accommodate the small amount of data left in the file we want to drop.

When I try a shrink through DBCC with the following command:

DBCC SHRINKFILE (N'fiAuditData Exp2' , EMPTYFILE)

I get the result:

DBCC SHRINKFILE: Moving page 4:16288391 failed.
Msg 2555, Level 16, State 2, Line 1
Cannot move all contents of file "fiAuditData Exp2" to other places to complete the EMPTYFILE operation.

It looks like there remains some housekeeping data in the file that prevents it from being dropped but I'm not sure. I have searched many other articles but have not found a reply that allowed me to successfully drop the table after shrinking it.

Any ideas how to go about getting rid of this data file would be greatly apperciated as the SHRINKFILE with the EMPTYFILE obviously does not work as intended.

Regards
Christo
GilaMonster
GilaMonster
SSC Guru
SSC Guru (911K reputation)SSC Guru (911K reputation)SSC Guru (911K reputation)SSC Guru (911K reputation)SSC Guru (911K reputation)SSC Guru (911K reputation)SSC Guru (911K reputation)SSC Guru (911K reputation)

Group: General Forum Members
Points: 911204 Visits: 48847
How many other files are in the same filegroup as the one you're trying to empty, how big are they and how much space is available in each?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Christo Steenkamp
Christo Steenkamp
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 18
Hello Gail,

There are 2 additional files - the original file that is roughly 80GB in size, with about 55GB available, and a 2nd file that is 50GB in size with 48GB available. Each file is on a separate physical disk.

In total there is more than 200GB of space available in the 230GB DB.

Let me know if you need additional information.

Regards
GilaMonster
GilaMonster
SSC Guru
SSC Guru (911K reputation)SSC Guru (911K reputation)SSC Guru (911K reputation)SSC Guru (911K reputation)SSC Guru (911K reputation)SSC Guru (911K reputation)SSC Guru (911K reputation)SSC Guru (911K reputation)

Group: General Forum Members
Points: 911204 Visits: 48847
All three in the same filegroup?
Is the one you're trying to empty the first file in the DB? (the mdf?)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Michael Valentine Jones
Michael Valentine Jones
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59098 Visits: 11933
Is the file you want to delete in the Primary filegroup?
Christo Steenkamp
Christo Steenkamp
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 18
No, the file I want to drop is the one added last - it is not the primary file that was created when the DB was created.

And yes, all 3 files are in the same File group.
EdVassie
EdVassie
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54434 Visits: 3984
In my experience, once you have used the EMPTYFILE keyword on a shrink, you must do a OS close and open on the file before you can drop it.

Stopping and restarting SQL Server will do the job.

Something less invasive is to put the affected database into read-only mode and then back to read-write.

If you are worried about impacting users, you can put the database into Autoclose and wait, maybe overnight or over a weekend. If there is a quiet time when no-one is using the database it will be closed. If you do use this method, you need to take off the Autoclose option as soon as you can, as each time the database is closed the next person who uses it faces a few seconds wait while it gets re-opened.

Whatever way you choose, once the files have been closed and opened you can drop them from the database.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Christo Steenkamp
Christo Steenkamp
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 18
Hallo Ed,

I will go try this in the morning and give feedback. I am not worried about user impact, so I will try the SQL stop/start solution.

It is just strange that the SHRINKFILE command does not in fact empty the table but gives the error... why would the re-start of SQL (open/close of the file) result in something different?

Regards
EdVassie
EdVassie
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54434 Visits: 3984
I am not sure what bit of the SHRINKFILE processing causes it to fail after the EMPTYFILE keyword is used, but fail it does. Even if you issue a SHRINKFILE ... EMPTYFILE and cancel the command my recollection is that all subsequent SHRINKFILES will quickly fail until the file gets reopened.

This means that cancelling a SHRINKFILE ... EMPTYFILE will leave the file not empty but unshrinkable until it is reopened. After it is reopened you can issue a new SHRINKFILE to clear out more data.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Christo Steenkamp
Christo Steenkamp
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 18
Hallo Ed,

OK, I executed a SHRINKFILE with EMPTYFILE switch again, got the same error with regards to being unable to move all data, namely

DBCC SHRINKFILE: Moving page 3:5866167 failed.
Msg 2555, Level 16, State 2, Line 1
Cannot move all contents of file "fiAuditData Exp2" to other places to complete the EMPTYFILE operation.

I then restarted SQL, and when I go to the Tasks for shrinking Files in the GUI I can see that there is about 1MB of data still in there, which of course mean I can still not drop the file. I tried this cycle a few times and no difference - as long as the data is in there I can not get rid of the file... so either I miss-understood your advice or I'm doing something wrong...

Any advice with regards to the next thing to try ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search