Deadlock in version 5.3.6.28
richard_collette@hsb.com
Posts: 12
Sql Prompt 5.3.6.28 caused a deadlock in a production system.
The lock message from our monitoring sofware was:
The deadlock victim was spid 200 with application name 'Red Gate Software Ltd SQL Prompt 5.3.6.28' by user xxxxxx' on host 'xxxxxx'.
Last command issued:
SELECT name, schema_id AS id FROM [tempdb].[sys].schemas; SELECT o.schema_id AS owner, o.name, o.type, o.object_id AS id, o.parent_object_id AS parent,
CONVERT(BIT, ( CASE WHEN o.is_ms_shipped = 1 THEN 1
WHEN x.major_id IS NOT NULL THEN 1
ELSE 0 END )) AS sysobj
FROM [tempdb].sys.objects o
LEFT JOIN [tempdb].sys.extended_properties x ON o.object_id = x.major_id
AND x.minor_id = 0 AND x.class = 1
AND x.name = N'microsoft_database_tools_support'
WHERE type != 'PK' AND type != 'TR' AND schema_id!=4;
SELECT object_id AS id, parent_id AS owner, name FROM [tempdb].[sys].triggers; SELECT user_type_id AS id, name, schema_id AS owner, system_type_id as systype, is_table_type FROM [tempdb].[sys].types;
SELECT name, id AS owner, xusertype, colid, CONVERT(BIT, (colstat & 1)) AS is_identity, CONVERT(BIT, (colstat & 4)) AS is_computed,
I can't imagine why a deadlock should occur just reading schema information.
The lock message from our monitoring sofware was:
The deadlock victim was spid 200 with application name 'Red Gate Software Ltd SQL Prompt 5.3.6.28' by user xxxxxx' on host 'xxxxxx'.
Last command issued:
SELECT name, schema_id AS id FROM [tempdb].[sys].schemas; SELECT o.schema_id AS owner, o.name, o.type, o.object_id AS id, o.parent_object_id AS parent,
CONVERT(BIT, ( CASE WHEN o.is_ms_shipped = 1 THEN 1
WHEN x.major_id IS NOT NULL THEN 1
ELSE 0 END )) AS sysobj
FROM [tempdb].sys.objects o
LEFT JOIN [tempdb].sys.extended_properties x ON o.object_id = x.major_id
AND x.minor_id = 0 AND x.class = 1
AND x.name = N'microsoft_database_tools_support'
WHERE type != 'PK' AND type != 'TR' AND schema_id!=4;
SELECT object_id AS id, parent_id AS owner, name FROM [tempdb].[sys].triggers; SELECT user_type_id AS id, name, schema_id AS owner, system_type_id as systype, is_table_type FROM [tempdb].[sys].types;
SELECT name, id AS owner, xusertype, colid, CONVERT(BIT, (colstat & 1)) AS is_identity, CONVERT(BIT, (colstat & 4)) AS is_computed,
I can't imagine why a deadlock should occur just reading schema information.
Comments
How often do you encounter this deadlock?
Whilst I have known SQL Prompt to encounter performance issues, I have never known it create SQL Deadlock.
Do you have any other Red Gate products that plug-in to SSMS installed? for example SQL Source Control, SQL Search or SQL Test.
Is there any information in the SQL Prompt logs for the date/time the deadlock appeared? You will find the SQL Prompt log files in this path:
C:\Users\<user_name>\AppData\local\red Gate\SQL Prompt 5
Many Thanks
Eddie[/code]
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
I do have those other products installed. I only use the Search application however.
Scanning through the logs, I only see exceptions for not being able to find the PK for a column, perhaps due to it being a table type.
The other errors in the logs are regarding not being able to decrypt system_internals_allocation_units and another regarding having no access to run DBCC TRACEON
I dont' see anything that appears to be related to the deadlock.