October 13, 2009 at 8:34 am
File ID 1 of database ID 9 cannot be shrunk as it is either being shrunk by another process or is empty.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I am getting the above error while trying to shrink a db, I have tried shrinking the db & files , nogo. 94 % space is free in the single datafile, around 15GB's. I have tried using SSMS or using DBCC shrinkdatabase/file, tried most options, like Truncateonly & all. I have tried going to the database properties, files & trying to decrease the size from there, nogo.
Any suggestions appreciated.
October 13, 2009 at 8:40 am
Auto-shrink enabled?
Why are you shrinking the database? Do you expect that the 15 GB free space won't be reused in a reasonable amount of time?
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
October 13, 2009 at 4:50 pm
Auto-shrink is enabled. Trying to shrink as need disk space. I myself hate shrinking as there is no point if the db grows back again, in this case there is so much space that I would like to shrink & then if it grows , then it grows, will get more disk space, but would like to try shrinking first. Thx for replying tho. I have tried shrinking small amount to all, nogo.
October 14, 2009 at 12:50 am
SQLRocker (10/13/2009)
Auto-shrink is enabled.
Disable it and try the manual shrink again. It could be that you're trying to shrink while the auto-shrink is running, hence the error that the file is being shrunk by another process.
Trying to shrink as need disk space. I myself hate shrinking as there is no point if the db grows back again, in this case there is so much space that I would like to shrink & then if it grows , then it grows, will get more disk space, but would like to try shrinking first.
Then get more disk space. Databases tend to grow, it's in their nature. All you're doing by shrinking then allowing it to grow then shrinking, etc is causing fragmentation at the file and index level and harming the performance of your app.
You should rebuild all indexes after a shrink, the fragmentation that shrink causes is usually not minor.
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
October 14, 2009 at 11:41 am
Sorry, I thought Auto-Shrink was ON, It is at OFF only as thats the default for all dbs.
I am following these 2 links tho:
http://weblogs.sqlteam.com/tarad/archive/2007/11/08/60394.aspx
http://support.microsoft.com/kb/960567/EN-US
I did DBCC UPDATEUSAGE on the db & will try again, Restarting SQL Server doesn't seem like a option tho as we don't have a maintenance window coming up & this is not a urgent issue.
As I said I too don't like shrinking dbs.
Thanks for your reply Gail, appreciate it.
October 15, 2009 at 11:50 am
Just FYI, I have been recommended to try this step:
Increase the size of the file a little bit & then try to shrink. I can't try this as we are not near a maintenance window & I am in the process of switching jobs.
April 21, 2015 at 11:20 am
SQLRocker
Thanks for the tip of expanding the file before shrinking. I was stuck trying to shrink my database file, where there was space in the file, but I couldn't shrink it. Expanding it by 1MB was enough for whatever internal file space value to be reset, and allow me to perform a shrink.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply