Quick one today:

A few weeks ago, we ran into an interesting error. This nebulous error has caused some confusion in the team, as it occurred during a high pressure situation. After the event passed, and everyone came back to normal state, it became apparent that this is a innocuous error.

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

One of our deployment steps changes the table definition of a large table. During this time, looks like, the database catalog is locked. That makes sense, as we are attempting to change the table definition which is in the catalog. Since this was a large table, the ALTER TABLE took more than a few minutes and for the whole duration of this change the catalog would be locked.

So, when we clicked on the tree structure in SSMS, there was no response. SSMS froze. After a few seconds, it came back with this error message. Surprisingly, if you query the catalog using TSQL, it works. So, that anomaly is not clear yet.

With in SSMS, under Options menu, go to, ‘Designers‘ >> ‘Table and Database Designers‘. On the right had side, look for ‘Override connection string time-out value for table designer updates: Transaction time-out after: 30 seconds‘. Change it to 60 or 90 seconds (as you see fit). This way, it will wait longer before times-out.

My preference is still 30 seconds. Once it errors out, I use TSQL to gather any information I need.

For more information, please refer to this MSDN article.