SQL 2005 logon timeout

Question

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Few things :

1.take help of sp_lock and sp_who2, and see if you can find/trace some suspect spid.

2.Any backend process/job at the same time.

3.read the errorlog

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

subbu_e SSCrazy Points: 2431 More actions · Answer 2

quick steps to identify the isssues are

1. run sp_who2 check any blocked SPID are there or not.

2. find out what is the blocked SPID T-SQL .

3. need to check is there any other batch job or process running on the server

vlad_us Valued Member Points: 61 More actions · Answer 3

this is production server under heavy load... many users and jobs work simultaneously...

no unusual locks or any deadlocks in trouble time...

and timeouts correlate with memory reallocation events (from last query from ms article)... but i can't find core of "freezing" of server

vlad_us Valued Member Points: 61 More actions · Answer 4

thanks all, the problem was mostly solved.

I found that timeouts occur when 100% load of some cpu cores. Optimization of stored procedurs was helped us.

The Microsoft article, which sent us on the right way:

subbu_e SSCrazy Points: 2431 More actions · Answer 5

it is always to have the patches or hotfixes updated with our SQL Server .:) great to hear that your issuse is resolved:-)

vlad_us Valued Member Points: 61 More actions · Answer 6

Not exactly. The patch has not helped, helped a phrase from the article:

... If you continue to have problems after you have installed this hotfix, troubleshoot the issue from an authentication and performance perspective.

Conclusion: Login timeouts occur when high cpu load (especially when 100% load on some cores) and many concurent connections.

Helps reduce the load on the processor, in our case: setting a parameter

max degree of parallelism from 0 to 2 and the cost threshold for parallelism = 30

and also optimization stored procedures and adding additional indexes.

Useful query to monitor cpu cores load:

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255