MS SQL Server: Verify Login Timeout

Product: MS SQL Server
Version: 2000 - 2016

Microsoft MS SQL Server pre-install with login timeout of 10sec.  Many developers or support personnel might have a chance to encounter login timeout while DBA might tell you that there is no login timeout, as they could be confused with other timeout values

There are 3 different timeout setting in MS SQL Server, so there is no surprise that a junior DBA mistaken one for other.

In the Internet, there is not much info about how to verify these 3 timeout setting, including login timeout.  This blog post is specifically to cover it.

How to Verify Using SELECT 

Uses following SELECT statement from any MS SQL Server client, such as Toad, DbVisualizer, SSMS, as well as programming.  This approach is not widely documented especially on verifying login timeout

select configuration_id, name, value_in_use from sys.configurations where configuration_id in (1519, 1541, 1520)

Sample output:

configuration_id	name	value_in_use
1519	remote login timeout (s)	10
1520	remote query timeout (s)	600
1541	query wait (s)	-1

Note: Above is default values for new MS SQL Server installation

You can see clearly that "remote login timeout" is 10 sec for default MS SQL Server installation.  DBA often confused that with "query wait" which is never, or no timeout.

Pros:

1. It is a regular SELECT statement that be easily written in any programming language
2. It is easily executes in web application
3. SELECT statement is 100% supported by any DB client, and utilities, e.g. Toad, Eclipse, Excel, DbVisualizer
4. Does not require to engage DBA to verify it
5. Execute once to see all 3 timeout values without hoping into various screens

Cons:

1. People who don't understand SELECT statement syntax might find it a lots of keys to enter
2. Not GUI base
3. Requires to run in SSMS or DB client
4. Junior DBA less familiar with this output, and might has more push back

How to Verify Using sp_configure

sp_configure statement can be used to both verify as well as set the database instance value.

Following statement can be used to verify 3 different timeout parameters.  You need to execute it 3 times as sp_configure command only allow to display 1 value

sp_configure 'query wait (s)'
go
sp_configure 'remote query timeout (s)'

go

sp_configure 'remote login timeout (s)'

go

Sample output:

Pros:

1. Simpler syntax compare to SELECT statement
2. Less typing
3. Junior DBA more familiar with this output
4. Statement is 100% supported by any DB client, and utilities, e.g. Toad, Eclipse, Excel, DbVisualizer
5. Does not require to engage DBA to verify it
6. Minor modification to the command will allow DBA to set the new value in real time

Cons:

1. Cannot obtain all 3 values in 1 statement
2. More effort in programming to integrate it, as well as get the 3 parameters
3. Not GUI base
4. Requires to run in SSMS or DB client

How to Verify Using GUI

This is the most commonly documented in MSDN or Technet.  All junior DBA are familiar with this approach, as there is no learning curve

Launch SSMS, open up the DB instance properties to verify these 3 timeout values

Pros:

1. GUI driven, and no need to memorize any statement or syntax
2. No typing requires
3. Junior DBA very familiar with this screen, and no push back
4. Quick & easy clicking on 2 screens to see all 3 timeout values
5. No need to memorize any command, such as SELECT and sp_configure

Cons:

1. Requires SSMS
2. Not supported by any DB client
3. Requires some skill to map the values in GUI to the sp_configure values
4. Impossible to written in any programming language
5. Impossible to execute in web application
6. Might need to engage DBA if firewall blocks SSMS
7. Needs to check 2 screens to determine all 3 parameters