March 2, 2009 at 5:56 am
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
March 2, 2009 at 6:03 am
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
March 2, 2009 at 1:10 pm
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
March 2, 2009 at 1:29 pm
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
March 2, 2009 at 1:30 pm
Is the file you want to delete in the Primary filegroup?
March 2, 2009 at 1:55 pm
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.
March 3, 2009 at 3:02 am
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: 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
March 3, 2009 at 4:53 am
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
March 3, 2009 at 5:17 am
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: 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
March 3, 2009 at 10:11 pm
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 ?
March 4, 2009 at 2:58 am
I assume you do have enough space in other file in the filegroup to hold the last bit of data? Otherwise all I can suggest is Googling this problem or raising a case with Micosoft.
One thing you can do is to shrink the file to the minimum possible size and set growth to zero. If you want to move it to a different disk you can use ALTER DATABASE to do this. This would mean the problem file still exists, but will not get any new data put in 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
March 4, 2009 at 3:40 am
Yes, there is enough space to accommodate the small bits of data remaining.
At this stage it is not allowing me at all to reduce the file size so I can't move them either. I was hoping to get a solution here - I have Googled extensively on the problem but have not found anything meaningful. I'm still hoping somebody here will be able to point me to a solution, else I'll open a case with Microsoft.
March 5, 2009 at 2:14 am
SOLUTION:
It turns out there is a bug in the SHRINKFILE Command - it has been fixed in SQL 2005 SP3 that is available for download. The SHRINKFILE with the EMPTYFILE switch now works as intended (After applying SP3) and the file could be dropped after the command completed.
Thanx to those that tried to help.
Regards
April 30, 2009 at 8:24 pm
Do u have any idea about bug id ?
Thanks
Mohit Gupta
May 2, 2009 at 3:26 am
Hi Mohit,
It is 959376. Refer http://support.microsoft.com/kb/959376/[/url] for more info on this fix.
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply