Home Dashboard Directory Help
Search

DBCC SHRINKFILE and SHRINKDATABASE failing after backup by SQL_Guru


Status: 

Closed
 as Not Reproducible Help for as Not Reproducible


5
1
Sign in
to vote
Type: Bug
ID: 291408
Opened: 8/6/2007 4:02:57 PM
Access Restriction: Public
4
Workaround(s)
view
7
User(s) can reproduce this bug

Description

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.
Details
Sign in to post a comment.
Posted by Ken C.1 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
Posted by VS_HCL 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 :).

Posted by Microsoft on 3/13/2009 at 12:04 PM
This has been fixed as part of a QFE.
Posted by Microsoft 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 (sunila@microsoft.com) for help. He is very knowledgeable and willing to help.
Posted by SQL_Guru 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.
Posted by Microsoft 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.
Posted by SQL_Guru 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.
Posted by Microsoft on 12/4/2007 at 5:32 PM
Thanks for your feedback. We will look into this.
Sign in to post a workaround.
Posted by roshan j on 3/9/2011 at 8:29 PM
This workaround worked in SQL Server 2008 as well..
Posted by Mike Wade on 3/10/2009 at 8:17 AM
I concur, this workaround does the trick.
Posted by mbourgon 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.
Posted by Kuljeet 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!

Regards,
Kuljeet