August 28, 2008 at 2:38 am
Hi Guys,
I am currently experiencing a problem with shrinking data files.
When i run the shrink job, the job succeeds but i get the following error:
File ID 4 of database ID 5 cannot be shrunk as it is either being shrunk by another process or is empty.
The data file won't shrink and there is about 180Gb of free space on the database. I ran DBCC updateusage as well.
Can anyone please provide me with a solution for this. I cannot detach/attach the db or restart the SQL services since it's a production db.
Please HELP!!!!
Regards
IC
August 28, 2008 at 3:23 am
How are you trying to shrink the file? Can you try to issue direct dbcc shrinkfile command from SSMS or QA and see what happened?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 28, 2008 at 3:26 am
how many files\filegroups does the database consist of?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 28, 2008 at 3:34 am
I have tried shrinking the file...and I get the error that i posted above.
August 28, 2008 at 3:36 am
4 filegroups exist on the database
August 28, 2008 at 3:50 am
my guess is 1 of the filegroups is already empty.
check your filegroups to see which are in use!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 28, 2008 at 3:51 am
Look whether your maintenance plans have shrinking included. Else, disconnect all the connections to the database and run a small shrink with a target size some 5-10 GB leseser than the total size specified to ensure that shrink works. If succesful, go for a smaller target size.
August 28, 2008 at 4:27 am
Please note that there are no Maintenance plans on the database...and all filegroups are enabled and data is being written to all files.
Please note the error as this is the problem when trying to shrink.
August 28, 2008 at 4:40 am
Imke Cronje (8/28/2008)
File ID 4 of database ID 5 cannot be shrunk as it is either being shrunk by another process or is empty.
yes and this message is fairly descriptive, have you physically checked that all filegroups are in use
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 28, 2008 at 4:54 am
found a solution.
Detach and attach the database and the run the dbcc shrinkfile and Viola, space regained.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply