Add column on table cause blocking

Mario2286 441 Reputation points
2020-08-19T06:21:43.707+00:00

I was adding column on one of my user table and found out that my alter table query blocked another query which i have no idea where does it come from .

ALTER TABLE [dbo].[table] ADD [column] [int] NOT NULL DEFAULT 0

My Add column SPID

and this is the query which has been blocked by my add column spid as below
18669-image.png

and this is the wait resource key

18610-image.png

This is object ID for index 281474978938880
18657-image.png

I believe a system query blocked by my add column query. Does anyone have idea what is that system query and why it is blocked when i m adding column on database which currently used by me only.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,232 questions
0 comments No comments
{count} votes

6 answers

Sort by: Most helpful
  1. tibor_karaszi@hotmail.com 4,301 Reputation points
    2020-08-19T11:54:12.043+00:00

    My guess is that it is Object Explorer in SSMS that generates this query. Should be easy to verify with a trace and clicking around a bit in OE.

    1 person found this answer helpful.
    0 comments No comments

  2. Mario2286 441 Reputation points
    2020-08-19T10:27:57.317+00:00

    This is the full scripts which is blocked by my Add column scripts

    SELECT tr.name AS [Name], tr.object_id AS [ID], CAST( tr.is_ms_shipped AS bit) AS [IsSystemObject], CASE WHEN tr.type = N'TR' THEN 1 WHEN tr.type = N'TA' THEN 2 ELSE 1 END AS [ImplementationType], CAST(CASE WHEN ISNULL(smtr.definition, ssmtr.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted] FROM sys.triggers AS tr LEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = tr.object_id LEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = tr.object_id WHERE (tr.parent_class = 0) ORDER BY [Name] ASC

    0 comments No comments

  3. Ronen Ariely 15,081 Reputation points
    2020-08-19T13:30:47.317+00:00

    Good day @Mario2286 ,

    According to your images, I am guessing that you use sp_who2. Right?

    If this is the case, then I recommend to check sp_whoisactive which is considered as better solution:

    https://github.com/amachanic/sp_whoisactive

    Anyhow, using sp_who2 (or sp_whoisactive ), the information is in your hand... just scroll up a bit from what we see in the first image and search session id 58. it seems like session ID 58 is blocked so using sp_who2 (or sp_whoisactive) you can get the information about which application executed this session - in the result set under the column "ProgramName"

    Anyhow, execute sp_whoisactive and check the column "program_name" in order to identify the application

    One more point...

    I believe a system query blocked by my add column query.

    You can use the is_user_process column in sys.dm_exec_sessions to tell which are system and which are user sessions

    Or again you can use the sp_whoisactive t get this information

    0 comments No comments

  4. Tom Phillips 17,706 Reputation points
    2020-08-19T18:41:39.51+00:00

    That is perfectly normal and expected.

    Alter table requires an exclusive schema lock, which blocks all access until it is done.

    SPID 59 is blocking itself, waiting for other threads to finish. Your table must be large and it is having to rewrite the table in parallel due to your alter table. This is perfectly normal and expected.


  5. AmeliaGu-MSFT 13,956 Reputation points Microsoft Vendor
    2020-08-20T03:21:56.917+00:00

    Hi Mario2286-5314,

    You can use the following query quoted from this article to view what query is blocking your Add column scripts:

    SELECT
    db.name DBName,
    tl.request_session_id,
    wt.blocking_session_id,
    OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
    tl.resource_type,
    h1.TEXT AS BlockedText,
    h2.TEXT AS BlockingText,
    tl.request_mode
    FROM sys.dm_tran_locks AS tl
    INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
    INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
    INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
    INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
    INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
    CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
    CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
    GO
    

    Best Regards,

    Amelia

    0 comments No comments