May 30, 2013 at 9:00 am
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?
May 31, 2013 at 12:48 pm
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]
May 31, 2013 at 1:38 pm
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