Unable to drop a file with SHRINKFILE and EMPTYFILE option

Question

Post reply

Unable to drop a file with SHRINKFILE and EMPTYFILE option

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

SequelDBA Ten Centuries Points: 1093 More actions · Answer 1

The KB references SQL 2008. Is there a KB for 2005?

Thanks

KU

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 2

Hi SequelDBA,

KB says this it APPLIES TO

Microsoft SQL Server 2008 Workgroup

Microsoft SQL Server 2008 Enterprise

Microsoft SQL Server 2008 Developer

Microsoft SQL Server 2008 Standard

Microsoft SQL Server 2005 Developer Edition

Microsoft SQL Server 2005 Enterprise Edition

Microsoft SQL Server 2005 Standard Edition

Microsoft SQL Server 2005 Workgroup Edition

You can refer to http://www.sqlserverdba.co.cc/2009/05/move-contents-between-files-within.html for more info on this error

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

wjones21 SSC Eights! Points: 944 More actions · Answer 3

wjones21

SSC Eights!

Points: 944

October 23, 2009 at 9:47 am

#1070127

I'm having the very same problem right now.

david.r.buckingham SSC Enthusiast Points: 106 More actions · Answer 4

The KB referenced is for a different message returned by SHRINKFILE than what is/was being reported and does not apply in this case, nor is it fixed in SP3. I too am encountering the same "Cannot move all contents of file XXX" in a SQL 2005 SP3 installation. I have done the same type of operation many times in SQL 2000 and a number of times without issue under SQL 2005 SP3, but this particular file is a problem. It should be noted that this file is in the same filegroup as others that I have already successfully eliminated and that there is about 5 times the amount of necessary space available in the other files in the filegroup.

David R Buckingham, MCSA, MCDBA

Soren Nielsen SSCommitted Points: 1768 More actions · Answer 5

Hi

I have same issue, added an extra file moved all data to the new file, and have now 1 MB left in the original file, and get the folly message on emptyfile:

Cannot move all contents of file "xxx" to other places to complete the emptyfile operation.

The whole reason for the shrink file was to deallocate data from a text column that did not seem to free up reserved space after delete of 25 Mill. rows.

Is the only option now a insert into a new table and then drop and rename, as suggested earlier in this article?

The weird thing is that most of the data is in new file, since there is only 1 MB in the original one, so I wonder what is stuck in the old file. Is there any way to see what pages are in a file, and what objects they belong to ?

//SUN

EdVassie SSC Guru Points: 60472 More actions · Answer 6

You can look at 'DBCC Page' to look at the contents. Google can show you how to do this.

My guess is that if the old file was the first file in the filegroup, then there are some space allocation pages in it that now do nothing but point to the new file. However, what you find will guide you on how to deal with it.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

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

david.r.buckingham SSC Enthusiast Points: 106 More actions · Answer 7

I will reaffirm that it is _not_ the original data file.

I have not used the DBCC command to directly inspect the contents, but that is a good idea next time I encounter it. I am no longer consulting at that company.

David R Buckingham, MCSA, MCDBA

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 8

Please execute the following query and check the results

select name,is_name_reserved from sys.database_files

If is_name_reserved =1 , then it indicates that it is waiting for a Log Backup to happen. Once you take a log backup, the file will be removed.

Thank You,

Best Regards,

SQLBuddy

Soren Nielsen SSCommitted Points: 1768 More actions · Answer 9

Hi,

Great tip I'll try that tomorrow, I think that DB ran simple recovery model, but still it might just had required the nightly backup, any way the other suggestion here about the DBCC PAGE command could also show something interesting......

//SUN

free_mascot One Orange Chip Points: 27169 More actions · Answer 10

I am facing the same issue to empty .ndf for tempdb. SQL Server 2005 SP4.

It's SP4 so solution SP3 will be eleminate.

It's tempdb and recovery is simple also getting value 0 for is_name_reserved.

Any hope?

Thanks in advance.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

DLathrop SSCrazy Points: 2018 More actions · Answer 11

Otherwise, the only thought I have, is that some of the tables could have been heaps, and now have forwarding records left in the un-emptyable file. If so, creating a clustered index on the heaps, then redoing the DBCC SHRINKFILE (N'filename' , EMPTYFILE) might solve the problem. Drop the clustered index afterwards to go back to heaps.

I don't think trying this would hurt anything (unless you're really dependent on the physical order of the rows in the heap, which would already be messed up).

Soren Nielsen:

It sounds like you're trying to drop the primary (original .mdf) file, which you can't do. (It has certain pages for system tables in fixed locations.)

David Lathrop
DBA
WA Dept of Health