none
Lock request time out period exceeded, error 1222" will appear when I try to expand the tables list in database in SQL Server Management Studio 2012 (Enterprises)

    Question

  • Hi All,

    Lock request time out period exceeded, error 1222" will appear when I try to expand the tables list in database in SQL Server Management Studio 2012 (Enterprises).

    I am creating partition on my table which is having more than 200 million records. Initially I thought this is happening due to ONLINE=OFF option is set for cluster index which will partition the existing table. But this is not true. I tried with ONLINE=ON & still I can see same problem.

    Below is the index which I am running to Partition my table:

    USE [MY_DB]
    GO
    BEGIN TRANSACTION

    CREATE CLUSTERED INDEX [TRANS_PS_635112970545378233] ON [dbo].[Table]
    (
    [Date]
    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [MY_PS]([Date])


    DROP INDEX [TRANS_PS_635112970545378233] ON [dbo].[Table]



    COMMIT TRANSACTION

    After running this query which is taking around 9-10 hours I am getting error: "Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)" when I try to expand the tables list in database in SQL Server Management Studio 2012 (Enterprises)

    My DBA suggested that need to snapshot_isolation_state =1 in sys.databases( SELECT * FROM sys.databases) at DB level. I am not sure so looking for opinion.

    http://technet.microsoft.com/en-us/library/ms178534.aspx


    Thanks Shiven:) If Answer is Helpful, Please Vote



    • Edited by S Kumar Dubey Monday, August 05, 2013 7:51 AM
    • Moved by Kalman Toth Monday, August 05, 2013 7:53 AM Not db design
    Monday, August 05, 2013 7:40 AM

Answers

All replies

  • I am moving it to database engine.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Monday, August 05, 2013 7:53 AM
  • How big Index size it is, the lock can be put for different reason as well!


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.


    • Edited by Rama Udaya Monday, August 05, 2013 8:20 AM updated
    Monday, August 05, 2013 8:19 AM
  • Usually for the big sizes it is good to do during non business hours & keeping restricted mode or single user mode is the good Idea to perform, but this will depends on how we approach so & knowing the data availability to the users

    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Monday, August 05, 2013 11:51 AM
  • This is normal and expected behavior in SSMS.

    You are creating an index, locking the table.  SSMS is unable to get the data about the tables,columns, indexes.  This is how it works.  You need to wait for the indexing to complete.

    Tuesday, August 06, 2013 1:42 PM
    Moderator
  • Hi  S Kumar Dubey,

    Tom Phillips’s opinion is correct, that is how it works, and this issue has been reported as a feedback for Microsoft.

    You could refer below articles named” SSMS Gets Blocked and Locks Up During Index Creation”. You could vote this issue under below link which will get more attention from Microsoft and it is helpful to get it resolved.

    As for set snapshot_isolation_state =1 in sys.databases( SELECT * FROM sys.databases) at DB level will be work?

    Actually, you even can't set this option, it will be suspect status and wait for this transaction completed. So it doesn't work.

    SSMS Gets Blocked and Locks Up During Index Creation

    http://connect.microsoft.com/SQLServer/feedback/details/478568

    Thanks,

    Candy Zhou


    Wednesday, August 07, 2013 7:14 AM