Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Forcing a query timeout in SQL Server

We have had an issue with a block of code that responds poorly in the face of slow databases (It craps the bed on a query timeout). We have created a patch, and are in the process of running it through regression.

We can't get a timeout. I've opened a transaction from SQL Mgmt Studio and updated every row to lock them, but that doesn't cause INSERTs to timeout (which is what I need).

Can I get a table-level lock easily via T-SQL? Or do I have to fiddle around in master? Or can I easily force the timeout without locking? Any input is appreciated.

like image 501
BnWasteland Avatar asked Apr 28 '09 14:04

BnWasteland


People also ask

How do I force a SQL timeout?

Try using the WAITFOR command in T-SQL. That'll pause the execution of the query and you should see a timeout. Try using the WAITFOR command in T-SQL. That'll pause the execution of the query and you should see a timeout.

How do you set a query timeout in SQL Server?

Using SQL Server Management StudioIn Object Explorer, right-click a server and select Properties. Click the Connections node. Under Remote server connections, in the Remote query timeout box, type or select a value from 0 through 2,147,483,647 to set the maximum number seconds for SQL Server to wait before timing out.


1 Answers

run this and then try your insert...

select * from yourTable with (holdlock,tablockx) 

here, you can lock it for 5 minutes:

BEGIN TRANSACTION  SELECT * FROM yourTable WITH (TABLOCKX, HOLDLOCK)  WHERE 0 = 1  WAITFOR DELAY '00:05'  ROLLBACK TRANSACTION 
like image 104
KM. Avatar answered Sep 28 '22 09:09

KM.