shrink fails with error - File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty

Question

Post reply

shrink fails with error - File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty

Deb Anderson

SSCrazy

Points: 2116
More actions
June 11, 2008 at 12:03 pm

#129519

This is an fyi (no response necessary).
I got this error and researched it and found no good answer for how to deal with it:
[font="Courier New"]"File ID 5 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty"[/font]
One post suggested that it was a memory issue, but it also suggested the only way to deal with it was to restart SQL. I don't have that luxury, so I cleared my cache and reran the shrink and the shrink worked.
[font="Courier New"]
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE[/font]
Thanks, Deb

Viewing 15 posts - 1 through 15 (of 33 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

If Autoshrink is "ON" and you tried to run a "dbcc shrinkfile" you could get that message.

* Noel

Paul Storm-444906 SSC Journeyman Points: 77 More actions · Answer 2

See the Connect bug "DBCC SHRINKFILE and SHRINKDATABASE failing after backup" at

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291408

Ted Henderson SSC Enthusiast Points: 152 More actions · Answer 3

One more thing to try that worked for me when I received this error message. I just increased the data file by a few MB and then tryed to do the Shrink again. It worked fine. It seems like adding a few MB to the data file resets an internal counter or switch that tells it it's not in the middle of a shrink now.

CoetzeeW Default port Points: 1411 More actions · Answer 4

thanks Ted, it worked for me as well....now to find out who is growing my tempdb 1 AM in the morning to rediculas sizes ..

Sunny1 SSC Eights! Points: 871 More actions · Answer 5

Sunny1

SSC Eights!

Points: 871

October 13, 2010 at 9:13 am

#1236296

Thanks Ted. It works for me, too.:-)

mbricker-600840 SSCertifiable Points: 5468 More actions · Answer 6

Whatta guy, Ted. Bailed me out also.

Mike

“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey

___________________________________________________________________

bhat.manjunath7 Valued Member Points: 54 More actions · Answer 7

Thanks A lot Ted. This worked for me:-).

Regards,

Manjunath C Bhat,

http://www.manjunathcbhat.in

Thanks & Regards,
Manjunath C Bhat,
http://manjunathcbhat.blogspot.com,
http://manjunathcbhat.wordpress.com

Vegard Hagen Hall of Fame Points: 3078 More actions · Answer 8

More often than not, the best thing to do is to not shrink at all though.

Kind regards,

Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

It is better to light a candle than to curse the darkness. (Chinese proverb)