What are the challenges you face when working across database platforms? Take the survey

Deadlock in version

Sql Prompt 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' 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.


  • Options
    Eddie DEddie D Posts: 1,792 Rose Gold 5
    Thank you for your forum post.

    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 Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Options
    This is the first time I have seen a deadlock occur.

    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.
Sign In or Register to comment.