SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Intermittent Connectivity/DB availability issue


Intermittent Connectivity/DB availability issue

Author
Message
OnTheNumbers
OnTheNumbers
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 316
Hi All,

I've got a problem intermittent Connectivity/DB availability issue
Here's quick low-down on the system
Windows 2008 R2 SP1
SQL 2005 SP4 64 bit
Physical machine 48 GB Ram 16 Intel Cores 2.9G
SAN attached storage.
Mirroing and LS in use.
DB~50GB


DB is used to manage application logons.
Application uses classic ASP
Combination of pooled and non-pooled connections
There are no interactive users to the DB

Generally usage occurs at set times of the day.
Intermittently, the db seems to become unavailable to the application.
Users cant login and start the application
Application is hosted across a number of servers and the connection error will appear on logs all of the app servers.
COM exception had occured in InitADODBConnection: ([Microsoft][ODBC SQL Server Driver]Timeout expired) Retrying 1 time...
COM Exception had occured in ProcessAudioNotificationRecords: [Microsoft][ODBC SQL Server Driver]Timeout expired
COM exception had occured: Operation is not allowed when the object is closed.
COM exception had occured in InitADODBConnection: ([Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.)

In the SQL Server Log we see
Error: 18456, Severity: 14, State: 27.
Then a number of these
Error: 18056, Severity: 20, State: 27.
Message
The client was unable to reuse a session with SPID 446, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message

Shortly before (say 2 mins) those errors On the SQL Server, I see the following in perfmon (I have a lot more data gathered so please ask)

Rapid rampup in user connections from ~500 to ~1100 (in 2 mins), plateauing for a 1min or so, then on to some higher value 1800+, plateauing for another

few mins before dropping back to ~500 by which time the outage is over.
Rapid decrease in bytes received & sent
Rapid decrease in Batch Requests/sec
No change in Total or Target Server memory
No change in the continual increase PLE
Rapid increase in Reserved Pages
Increase in %processor time from 5 to 10%
No deadlocking

I've got profiler data that seems shows some activity during the outage but desipted the increase in user connection no new users can login to the application.
The system invariably sorts its self out and goes back to normal. The duration of these can be from less than a 1 min to 10.

I've been looking at this for quite a while can't definitively point the finger at where the problem is and could really do with fresh opinions/ questions. As I said, I've got quite a bit of data from perfmon/profiler and the DMVs so please ask for whatever may help.
Hemant.R
Hemant.R
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 240
please can u check with application team ,how many user connection they have mention in application.
if that exceed problem will arise.

or u can used below query for no connections on server

SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY login_name;
OnTheNumbers
OnTheNumbers
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 316
Hiya Hemant.R

No sure what you mean by
please can u check with application team ,how many user connection they have mention in application.
if that exceed problem will arise
.

Profiler indicates connections going from its steady state of ~500 to ~1100 (in 2 mins), plateauing for a 1min or so, then on to some higher value 1800+, plateauing for another. There is only one login connecting to server.....apart from me, obviously

OnThenNumbers
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search