Redgate query blocked by ALTER TABLE ALTER COLUMN

a.higgins

Right now, I'm running an ALTER TABLE ALTER COLUMN statement in one of my databases. 

While that's running, there's a blocked SPID (program_name = 'Red Gate Software - SQL Tools') that's attempting to run this statement on the same database:

SELECT sm.object_id AS id,
definition AS text,
1 AS colid,
(CASE WHEN OBJECTPROPERTY(so.object_id, N'IsReplProc')<>0 OR so.type = 'P' THEN 1 ELSE 0 END) AS number,
CAST(CASE WHEN sm.definition IS NULL THEN 1 ELSE 0 END AS bit)  AS Encrypted, -- https://msdn.microsoft.com/en-us/library/ms175081.aspx
CONVERT(bit, CASE WHEN so.type = 'D' AND so.parent_object_id != 0 THEN 1 ELSE 0 END) AS IsDefault,
ISNULL(sm.uses_quoted_identifier, 0) AS QuotedIdentifier,
NULL AS ctext
FROM sys.sql_modules sm WITH (NOLOCK)
LEFT JOIN sys.objects so WITH (NOLOCK) ON so.object_id = sm.object_id
WHERE (so.type in ('P', 'V', 'TR', 'TA', 'IF', 'FN', 'TF', 'FS', 'FT')
OR (so.type = 'D' AND so.parent_object_id != 0)
OR sm.object_id IN (SELECT object_id FROM sys.triggers WITH (NOLOCK) WHERE parent_class=0))
UNION ALL
SELECT d.object_id AS id,
d.definition AS text,
parent_column_id AS colid,
0 AS number,
CAST(0 AS BIT) AS Encrypted, -- Not encryptable https://msdn.microsoft.com/en-us/library/ms173758.aspx
CAST(1 AS BIT) AS IsDefault,
CAST(0 AS BIT) AS QuotedIdentifier,
NULL AS ctext
FROM sys.default_constraints d WITH (NOLOCK)
UNION ALL
SELECT
p.object_id AS id,
p.definition AS text,
CAST(1 as int) AS colid,
procedure_number AS number,
CAST(CASE WHEN p.definition IS NULL THEN 1 ELSE 0 END AS bit) AS Encrypted, -- https://msdn.microsoft.com/en-us/library/ms179865.aspx
CAST(0 as bit) AS IsDefault,
m.uses_quoted_identifier AS QuotedIdentifier,
NULL AS ctext
FROM sys.numbered_procedures p WITH (NOLOCK)
LEFT JOIN sys.sql_modules m WITH (NOLOCK) ON p.object_id = m.object_id

ORDER BY id, colid, number

It doesn't look to me as if the query really needs to know what the column data type is, so I'm guessing this is just due to the use of the OBJECTPROPERTY() helper function: see Bad habits: Using (certain) metadata "helper" functions (sentryone.com). 

Kurt_M

Hi there,

With regards to your blocked issue, I have managed to replicate this on my machine. From what I can see, when you have a transaction running on the DB for the ALTER TABLE ALTER COLUMN statement, SQL Compare hangs and stops functioning due to the lock on the table. 

Until the lock is cleared, SQL Compare will not finish whatever it's trying to do (in my testing I was just trying to compare my two databases) and will not produce any logs related to the hanging as it hasn't actually produced any errors.

I did do some testing around your link where it doesn't like the OBJECTPROPERTY() function, which you are correct on, but, various other portions of the query are also having issues running due to the lock. Specifically the 'definition' column in the sys.sql_modules table. It hangs when this is queried. As this issue is related to the table lock, there isn't anything else that can be done apart from clearing the lock to let SQL Compare function normally.

a.higgins

Kurt, thanks very much for the detailed response!!!  Given that you've been able to confirm that the deadlock behavior would happen regardless, I am happy to let things lie as they are ... the tool, as usual, is doing its job well. 

Thanks again!