which is the best way to implement blocking queries or SP's in sql server 2008 r2

Question

I am using SQL Server 2008 R2, and I have to implement the process like if there is any blocking on the production server it will immediately insert into the blocking table.

Which is the best way to implement this process?

Answer 1

I assume that you're saying that if the query you're running can't be performed immediately, then you'd like to insert data into a different table instead (perhaps to be tried again later)?

That's probably going to best be handled in your application.

For example, in C# (or any .NET language), the SqlCommand class has a property called CommandTimeout. You could set this timeout to a low value (say, 1 second). If the command takes longer than 1 second, you'd then catch the SqlException that is raised and perform the insert instead.