Dacpac - SQL Server error 1222 lock request time out period exceeded

  • Hi,

    I am using dacpac for database upgrade.
    When applying dacpac in database , below error is raised.
    SQL Server error 1222 lock request time out period exceeded (SQL72014) 

    database is size is 130 gb and error is raised when creating big table having picture content table with FILESTREAM

    pls advise a solution for fix erorr

    Regards
    Binu

  • binutb - Sunday, March 26, 2017 9:20 AM

    Hi,

    I am using dacpac for database upgrade.
    When applying dacpac in database , below error is raised.
    SQL Server error 1222 lock request time out period exceeded (SQL72014) 

    database is size is 130 gb and error is raised when creating big table having picture content table with FILESTREAM

    pls advise a solution for fix erorr

    Regards
    Binu

    DACPACs do not contain data, so I do not understand your comment about creating a 'big table' ... please elaborate.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Dacpac do the following steps if have any structure change between database table and structure in dacpac.
    1. Dacpac will create a temporary table with dacpac structure.
    2. Copy all records from database table to temporary table.
    3. Drop the orginal table.
    4. Rename the temporary table to orginal table

    seems to that time out is happend when copy records from database table to temporary table.

  • binutb - Sunday, March 26, 2017 9:23 PM

    Dacpac do the following steps if have any structure change between database table and structure in dacpac.
    1. Dacpac will create a temporary table with dacpac structure.
    2. Copy all records from database table to temporary table.
    3. Drop the orginal table.
    4. Rename the temporary table to orginal table

    seems to that time out is happend when copy records from database table to temporary table.

    When you deploy to a large database, you should review the deployment script before running it. If you don't like the way the script works, don't execute it.

    In this case, you will probably find that you can script the modifications yourself in a more efficient way.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Hi All,

    Anybody have any idea about the error 
    SQL Server error 1222 lock request time out period exceeded (SQL72014)    - when apply dacpac

    Regards
    Binu

  • I'm still not 100% sure what question you are asking.
    But are you trying to do the deployment while the database is in use? If so, the message is completely understandable – have you Googled it to ensure that you know why it is happening?
    So I suspect that you have two issues going on here:
    a) The script generated by the VS deployment executable requires a huge amount of IO & this takes time
    b) The deployment is trying to obtain a lock on the table it is attempting to modify and cannot, possibly because the database is in use at the time of deployment.
    A possible solution is
    c) Write the deployment script yourself, for the problematic table, in such a way that you are using ALTER TABLE, rather than using temp tables.
    d) Execute the script when there is no other database activity.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

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

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