Sql-server – Identifying “Lock Timeout” errors on SQL Server 2000

Sounds like a timeout setting. So your SSMS thinks it takes too long and cancels the connection for you. The SQL server roles back. But there is help. You are not the first person to encounter this.

See here.

For everybody who doesn't want to click the link. Here is the price winning answer:

After hitting the same error, I stumbled upon the corrent setting.

In the Management Studio, from the Tools menu, select Options, then click "Designers". There is an option called "Override connection string time-out value for table designer updates:" In the "Transaction time-out after:" box, you will see the magic 30 seconds

Make sure the "Auto-Close" property of the database is set to FALSE.

If this is on, then during times of low traffic, it basically takes the db offline. The first query against it attempts to bring it back online, but depending on the size, that might take awhile.

EDIT: If that's not the case, then a few other things to check:

• Does your SQL server try to run IO-intensive virus scans or something during times of low activity? If so, make sure to exclude your SQL folders.
• Any other pieces of your app that might be responsible here? For example, if you are using IIS with a .Net web app, resetting the application pool would force pages to re-compile the next time they are called.

Beyond this, the only thing I can think of (short of upgrading to SQL 2008 Enterprise Edition with Resource Governer) is to optimize your query so that it performs optimally even when the table's aren't cached.

All the normal performance tuning recommendations would of course apply, including proper indexing (preferably covering indexes), etc.