0

The default SQL Trace of my instances shows the following issue for native SQL logins (non-domain accounts):

Network error code 0xe8 occurred while establishing a connection; the connection has been closed. This may have been caused by client or server login timeout expiration. Time spent during login: total 18230 ms, enqueued 2 ms, network writes 5 ms, network reads 128 ms, establishing SSL 71 ms, negotiating SSPI 0 ms, validating login 18096 ms. [CLIENT: ]

I've done some digging and haven't been able to find what tolls to use or metrics to gather to better assign the login validation performance issue.

This is SQL Server 2005 with Service Pack 3 on Windows 2003 R2 with Service Pack 2 on VMware 4.1.0 with the latest VMware tools installed.

Improve this question

2 Answers 2

Reset to default
1

What edition of SQL Server? Have you checked whether you have logon triggers enabled? (Your logon trigger could have a performance issue.) 

USE master;
GO
SELECT * FROM sys.server_triggers
  WHERE OBJECT_DEFINITION([object_id])
  LIKE '%FOR%LOGON%';

Definitely sounds like most of the time was spent validating login - so if it's not a logon trigger, then maybe it's some other resource constraint - for example, how many logins in the system?

SELECT COUNT(*) FROM master.sys.server_principals;

How many databases?

SELECT COUNT(*) FROM master.sys.databases;

How many of the databases associated with the SQL Server login you are using are set to auto-close?

SELECT COUNT(*) FROM master.sys.databases WHERE is_auto_close_on = 1;

Does the same symptom happen if you log in with sa, or is this isolated to a specific SQL login?

Improve this answer
1
  • None of the above cases were affecting us. I failed to mention this is the Express SKU with 5 <1GB databases and about 3-5 active users. On a hunch, we removed the SEP client and have noted significant improvement all without connection timeouts. The filter-level network driver has been an issue in the past, so we'll test deploying an AV-only package of SEP at a later date.
    – sqlreader
    Dec 7, 2011 at 19:01
0

On a hunch, we remove the Symantec Endpoint Client which was a full deployment. We've had issues with the Network filter-level driver in the past.

On reboot we saw significant improvement in general performance and the login timeouts. We'll adjust the SEP deployment for AV only to eliminate the troublesome network filter driver.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .