Database shrink Error
I know shrinking data files are not good, but sometimes we have to do it when we have some space issue. I don't do this on a regular basis but when i tried shrinking the data files i got his error below: any idea???
Shrink failed for Datafile 'IBM_Core_1'. (Microsoft.Sqlserver.smo)
Additional information:
An expception occured while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Conne
A server error occured on the current command. The results, if any, should be discarded.
File ID 6 of Database ID 2 cannot be shrunk as it is either being shrunk by another process or is empty. (Microsoft SQL Server)
Shrink failed for Datafile 'IBM_Core_1'. (Microsoft.Sqlserver.smo)
Additional information:
An expception occured while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Conne
A server error occured on the current command. The results, if any, should be discarded.
File ID 6 of Database ID 2 cannot be shrunk as it is either being shrunk by another process or is empty. (Microsoft SQL Server)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can try doing this just to be sure:
ALTER DATABASE database_name SET SINGLE_USER;
DBCC SHRINKDATABASE (database_name);
-- TURN BACK MULTI USER
ALTER DATABASE database_name SET MULTI_USER;
ALTER DATABASE database_name SET SINGLE_USER;
DBCC SHRINKDATABASE (database_name);
-- TURN BACK MULTI USER
ALTER DATABASE database_name SET MULTI_USER;
ASKER
i had an idea about that but any other idea?
setting single seems a bit confusing since client are using it at all times.
setting single seems a bit confusing since client are using it at all times.
sp_who2 active
shows no other user performing a shrink operation?
shows no other user performing a shrink operation?
ASKER
no one else does the shrink. i am the only DBA followed by my manager. so no one touches the database.
Have a look at some suggestions here
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/581393f0-37bd-4521-9c6b-44fb37858b17
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.
Close Management Studio then launch Management Studio again and try the shrink again
take the database offline and then bring it back online.
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/581393f0-37bd-4521-9c6b-44fb37858b17
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.
Close Management Studio then launch Management Studio again and try the shrink again
take the database offline and then bring it back online.
ASKER
thanks
ASKER