I am seeing calls like those shown below in my SQL Server Audit logs but have no idea what it means. A google search turned up a possible relationship with compiles/recompiles. The line (or similar lines) can also be found inside several SQL Server internal sprocs.
EXEC %%Object(MultiName = @objname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
Does anyone have an understanding of this process?
It's an internal method, presumably - from the name - used to lock an object based on its
object_id and ensure that the executing user has the rights to make further changes to the object. This locking is required to do things like adding/dropping properties, hidden columns, etc., and to ensure that the object can't be dropped or altered by others in the meantime (mere mortals tend to do this by starting a transaction and locking the table through some indirect method, since there is no
LOCK TABLE command - but clearly there is internal plumbing to help with system operations).
If you look at any of the procedures where this is used (there is a large list):
SELECT N'EXEC sp_helptext ''sys.' + QUOTENAME(name) + N''';' FROM sys.all_objects WHERE [schema_id] = 4 AND LOWER(OBJECT_DEFINITION([object_id])) LIKE N'%lockmatchid%';
You'll see preceding comments like:
-- Lock the table schema and check permissions -- Share lock default so it cannot be dropped -- Since binding a default is a schema change, update schema count -- for the object in the sysobjects table. -- Ex-lock and check permission -- Share Lock rule so that it cannot be dropped -- Check that table and column exist -- RESOLVE OBJECT NAME -- Get exclusive object lock upfront so subsequent %%ColumnEx can proceed -- Acquire sch-M lock up-front on the published object -- LOCK PROC & CHECK PERMISSION -- -- LOCK, CHECK PERMISSION, BUMP SCHEMA FOR RECOMPILE -- -- obtain owner-qual object name -- Re-acquire schema mod lock to make the code more resilient to changes -- although this must have been done inside sp_Mrepl_schema -- LOCK TABLE, CHECK STANDARD TABLE-DDL PERMISSIONS -- Since binding a default is a schema change, -- update schema count for the object in the sysobjects table. -- Update schema count for the object in the sysobjects table. -- Verify that table exists
(And people say commenting code is a waste of time.)
There also seems to be
LockExclusiveMatchID method, though the
Exclusive argument seems to have later been added to
LockMatchID, which made the exclusive method obsolete.
Not sure how much more specific you need to this to be - if you want to track down where this a high occurrence of these, you need to look at the outer calls, not this specific statement. For example, do you have an application that is renaming objects a lot, or calling
sp_autostats directly, changing replication, or anything else you can infer from the list generated above? You'll have to figure out which procedures are getting called to help determine the why. And what you'll do about it (if you can even determine that there is a "problem") won't have anything to do with
I got this error when trying to delete a Local Publication in SQL 2012, from SSMS.