By Kendra Little on August 20, 2024
At times when shrinking a data file in a SQL Server or Azure SQL Managed Instance/Database, shrink operations may persistently fail with the error:
Msg 1119, Level 16, State 1, Line 11 Removing IAM page ([filenumber]:[pagenumber]]) failed because someone else is using the object that this IAM page belongs to. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
There’s not much documented on this error anywhere that I can find, so I’m sharing my experience with this error. tldr: I was not able to get past this without restarting the SQL Server service.
How to get past the error
So far, the only way I’ve found to get past this error is by restarting the SQL Server service, or accomplishing this by triggering a failover in a managed database environment. I’ve asked Microsoft Support to document this error and if there are any fixes other than a service restart.
SHRINKFILE with NOTRUNCATE may be helpful if you can’t restart the SQL Server Service right away
If you can’t restart the SQL Server service right away, I have found that if you can run the DBCC SHRINKFILE command with the NOTRUNCATE option without hitting the error, this can help you make progress on shrink while you wait for a service restart.
(To be clear, this won’t actually shrink any files– it will only relocate pages within the datafiles. This can make subsequent shrinks move more quickly once you can remove the NOTRUNCATE option from your command without hitting an error.)
Things that did not work
I was not able to identify what object the IAM page belonged to– I believe it was used in a system object and I didn’t have the right breadcrumb trail to identify it. There were no longrunning open transactions on the instance. Stopping things like Extended Events traces didn’t help. Running SHRINKFILE with various permutations didn’t help either– shrinking smaller sizes worked to a point, then everything (except shrinking with NOTRUNCATE) on the data files would stop with the error. The error also happened on multiple data files with different IAM page numbers.
Microsoft Support recommended running checkdb, which came back clean. They didn’t actually recommend the service restart.
REGISTER