Removing IAM page %S_PGID failed because someone else is using the object that this IAM page belongs to

-> I was executing DBCC SHRINKFILE as an one time operation on a huge data file to move its data into 4 additional data files using below command,

USE [Financedb]
GO

DBCC SHRINKFILE (N'Finance_Data' , EMPTYFILE)
GO

-> I received below error when executing above command,

Msg 1119, Level 16, State 1, Line 20
Removing IAM page (3:5940460) failed because someone else is using the object that this IAM page belongs to.

-> I executed DBCC CHECKDB and there were no errors,

DBCC results for ‘Financedb‘.
.
.
CHECKDB found 0 allocation errors and 0 consistency errors in database Financedb.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-> This is not a corruption issue. I restarted SQL Services and executed below command and it ran through.

USE [Financedb]
GO
DBCC SHRINKFILE (N'Finance_Data' , EMPTYFILE)
GO

Msg 2555, Level 16, State 1, Line 20
Cannot move all contents of file “Finance_Data” to other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-> Above message is due to the fact that System objects cannot be moved.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements