SQL Server Home
DBCC SHRINKFILE and SHRINKDATABASE failing after backup
as Not Reproducible
8/6/2007 4:02:57 PM
User(s) can reproduce this bug
When trying to shrink a file or database I get an error if a backup tries to run at the same time.
"File ID 1 of database ID 21 cannot be shrunk as it is either being shrunk by another process or is empty."
This occurs if I am attempting to shrink a file, data changes are happening in the database, and either a log backup or full backup is ran. After the error happens, I ALWAYS get this error until I restart the SQL Service or take the database offline and online again.
I've seen this error since RTM and still see it in build 3175. I've seen it in Developer Edition, Standard Edition, and Enterprise Edition. I've also seen it in both 32 bit and 64 bit.
SQL Server 2005 SP2 - Enterprise Edition
Win2003 Standard Server (SP2)
Operating System Language
Steps to Reproduce
I've tested this using a 1 GB database, but it is even more of an issue in large databases. The one I tested with was called TestDB. It only had 1 data file in the database and was using SIMPLE recovery.
Open 3 query windows.
backup database TestDB to disk = 'nul'
dbcc shrinkfile ('TestDB', 1)
create table t (a int)
insert t values (1)
Run all queries at the same time and you will eventually get the error message and will not be able to shrink the file again until you take it offline or restart the server.
I get an error mesage stating that something else is currently shrinking the file.
"File ID 1 of database ID 21 cannot be shrunk as it is either being shrunk by another process or is empty.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded."
Sometimes the error will go away after a short time, but usually it doesn't.
I shouldn't get this error message unless something else is really shrinking the file or database or the file is actually empty. And I should be able to shrink the database in the future once the condition that caused it is gone.
to post a comment.
Please enter a comment.
on 7/9/2010 at 11:43 AM
We have SQL 2005 Enterprise with SP3 and occasionally still ran into the same problem. Any idea where I can download any patches that fixe the problem without the work around?
BTW, thanks for the workaround
on 12/21/2009 at 3:51 PM
Strange workarounds for MS products, I think we should get used to living with it, unless Google goes bust :).
on 3/13/2009 at 12:04 PM
This has been fixed as part of a QFE.
on 6/11/2008 at 12:53 PM
Thanks for the update.
At this time, we don't know whether we have any plan to do SQL Server 2005 SP3. If SQL Server 2005 SP3 is in the plan, I will try my best to convince my manager to take this bug. Other than that, I cannot promise anything.
I updated the release version so that this bug would be considered in SQL Server 2005 SP3.
If you have any further concern, please contact my program manager (firstname.lastname@example.org) for help. He is very knowledgeable and willing to help.
on 6/11/2008 at 12:36 PM
My mistake, I put in the version incorrectly. It was in SQL 2005, not SQL 2000. You can tell from the build I mention, 3175.
We have 20+ servers and about half are running SQL 2005, but the others are still running SQL 2000. Waiting until we upgrade all of them to SQL 2008 would not make sense, especially since the SQL 2005 boxes probably won't be upgrade to 2008 until after the 2000 boxes make it to 2005.
on 6/11/2008 at 11:42 AM
From the bug, it showed that the issue happened in SQL Server 2000; however, from the description, it looked that this is a SQL Server 2005 issue. Please confirm that the issue happened in SQL Server 2005.
At this time, we had no plan to fix it in SQL Server 2005 unless this is from an QFE request.
I remembered fixing similar issue in SQL Server 2008. So your best bet is to wait for the RTM of SQL Server 2008 (which will happen around Junly 31st). Believe me, SQL Server 2008 is a much better product (quality-wise) than SQL Server 2005.
on 6/11/2008 at 8:46 AM
Any luck in reproducing this issue? I would love if it could be fixed and included in SP3.
on 12/4/2007 at 5:32 PM
Thanks for your feedback. We will look into this.
to post a workaround.
Please enter a workaround.
on 3/9/2011 at 8:29 PM
This workaround worked in SQL Server 2008 as well..
on 3/10/2009 at 8:17 AM
I concur, this workaround does the trick.
on 12/13/2008 at 8:17 AM
Ksingh is right - growing the database file that you're trying to shrink, in my case by 50mb, seems to fix the problem.
on 10/1/2007 at 2:04 PM
This worked for me - I increased the file size of the database file by a few MBs after which I was successfully able to issue the DBCC SHRINK file command.
This may or may not be of importance how ever the very first statement that I ran after increasing the file size was DBBCC SHRINKFILE command using the TRUNCATEONLY flag.
All the best!
© 2013 Microsoft