To avoid DB LOCKS i have set my SQL Server LOCK_TIMEOUT to 10,000 (10 sec) but from many days, i observed that it automatically kept changing to -1 (no time out) and i kept receiving calls from my users that some process are locked.
I tried to investigate that how it gets changed by using Profiler and found that every time i open Microsoft SQL Server Management Studio and press New Query; it automatcally sends following command to the SQL Server SETTING all the values including LOCK_TIMEOUT..
SET ROWCOUNT 0 SET TEXTSIZE 2147483647 SET NOCOUNT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ARITHABORT ON SET LOCK_TIMEOUT -1 SET
QUERY_GOVERNOR_COST_LIMIT 0 SET DEADLOCK_PRIORITY NORMAL SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET ANSI_NULLS ON SET
ANSI_NULL_DFLT_ON ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CURSOR_CLOSE_ON_COMMIT OFF SET IMPLICIT_TRANSACTIONS OFF SET
QUOTED_IDENTIFIER ON
My question is that why it SETS the server values? I went to Query Options and tried to change but it changes only for the current query screen, if i close/open the query window, it again runs the below commands SET LOCK_TIMEOUT -1. Can anyone help me in this?
Start Free Trial
