March 26, 2017 at 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
March 26, 2017 at 3:00 pm
binutb - Sunday, March 26, 2017 9:20 AMHi,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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 26, 2017 at 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.
March 27, 2017 at 5:41 am
binutb - Sunday, March 26, 2017 9:23 PMDacpac 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 tableseems 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 27, 2017 at 9:09 pm
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
March 28, 2017 at 4:44 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply