Locking while using sp_updateextendedproperty

  • I am seeing locking/blocking when using sp_addextendedproperty and sp_updateextendedproperty.

    BOL doesn't mention this behaviour can anyone shed any light onto what is happening and why?

  • You can always wrap your statement inside of a BEGIN TRAN and ROLLBACK and check sys.dm_tran_locks to see what locks it will take.

    I ran a quick test using this example from Microsoft against AdventureWorks:

    USE AdventureWorks2008R2;

    GO

    BEGIN TRAN

    EXEC sp_addextendedproperty

    @name = N'Caption',

    @value = 'Postal code is a required column.',

    @level0type = N'Schema', @level0name = 'Person',

    @level1type = N'Table', @level1name = 'Address',

    @level2type = N'Column', @level2name = 'PostalCode';

    GO

    It took a Sch-S lock on the schema and a Sch-M lock on the table. I'd bet that Sch-M lock on the table is what might be causing you problems.

    Twitter: @SQL_JGood
    Blog: sqljgood.wordpress.com/[/url]

  • You are correct. Turned out to be a 3rd party app holding a schema lock.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply