Altering a table is taking lot of time

  • Shadab Shah

    SSCarpal Tunnel

    Points: 4759


    I have a table , having 900 records. I tried altering the table but it is taking decades to alter a table with just 900 records.

    The table was not having a PK, so i thought of adding a PK Constraint , but that query is also taking too long.

    Can anybody please help me to understand what is actually the problem over here.


  • AlexSQLForums


    Points: 14249

    Oops double post.

    Alex S
  • AlexSQLForums


    Points: 14249

    You may have another process accessing this table.

    Check and wait or kill







    t2.blocking_session_id, 'object name',

    o1.type_desc 'object descr',

    p1.partition_id 'partition id',

    p1.rows 'partition/page rows',

    a1.type_desc 'index descr',

    a1.container_id 'index/page container_id'

    FROM sys.dm_tran_locks as t1

    INNER JOIN sys.dm_os_waiting_tasks as t2

    ON t1.lock_owner_address = t2.resource_address

    LEFT OUTER JOIN sys.objects o1 on o1.object_id = t1.resource_associated_entity_id

    LEFT OUTER JOIN sys.partitions p1 on p1.hobt_id = t1.resource_associated_entity_id

    LEFT OUTER JOIN sys.allocation_units a1 on a1.allocation_unit_id = t1.resource_associated_entity_id

    Alex S
  • Bhaskar.Shetty

    Hall of Fame

    Points: 3054


    Check the activitylog and check if any active transactions locking the table, 900 records is not at all a big table.

  • free_mascot

    One Orange Chip

    Points: 27168

    Are you doing with GUI or commandline i.e. T-sql?

    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Grant Fritchey

    SSC Guru

    Points: 395510

    Sounds like it's probably blocking from other resources accessing the table. Otherwise, it shouldn't take that long at all.

    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply