| Author |
Topic |
|
tcrkay
Starting Member
5 Posts |
 Posted - 2008-07-14 : 17:11:27
|
| HiWe have a database server (with sql 2000) and a webs server (both running windows server 2003) but today we've started to experience a weird connection problem between the two...Every few hours it seems to grind to a halt and an error is displayed on any website which pulls records from the database. It's not always the same error - sometimes either general network error, permission denied, server not found and timeout expired.My connection string looks like (obviously blanked the details):"Driver={SQL Server};Server=123.123.123.123;UID=xxxxxxx;Network=DBMSSOCN;Database=XXXXXX;PWD=XXXXXXX"The the sites are written in classic ASP and are quite busy.Apologies if it sounds a bit vague - not sure exactly what other details might be needed.Thanks for any helpTrevor |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-14 : 17:19:05
|
| Sounds like you've got performance issues somewhere. It's hard to provide more information given the vague information.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
tcrkay
Starting Member
5 Posts |
Posted - 2008-07-14 : 17:27:32
|
| I thought there may be an issue with the connection between the 2 servers but it pings <2ms and file transfer is normal.I have managed to find one of the error messages:Microsoft OLE DB Provider for ODBC Drivers error '80004005'[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation. I should also add the problem dissapears for a while after both servers are rebooted.ThanksTrevor |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-14 : 17:30:18
|
| Run SQL Profiler and Performance Monitor to determine the load on the server. Make appropriate changes based upon the analysis done using the data the tools output.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tcrkay
Starting Member
5 Posts |
Posted - 2008-07-21 : 03:23:20
|
| I'm not really that familiar with profiler or performance monitor but I did notice the server was dangerously low on ram - only 1gb installed! Anyway I had this upgraded to 4 and all seemed well until the same problem struck again...This time I had a good look around before restarting the server and noticed that there we hundreds of processes idle within management> current activity> process infoThe vast majority of these were using one of two different databases - both of which sites use similiar code.My question is, is this a flaw in the code (I'm sure the db connection is closed after each use) or a bug in sql? Nothing major has been changed on either site recently. I also noticed sql is only on SP2 - would upgrading to SP4 help?Thanks for you helpTrevor |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-21 : 11:38:04
|
| It is normal to see idle connections. There is no such thing as SP4 for SQL Server 2005, yet that is. SP3 is supposed to be released later this year, so SP2 is the most current service pack. You should get on the latest build. I'd install at least cumulative update package 7. I hear 9 is available though.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tcrkay
Starting Member
5 Posts |
Posted - 2008-07-21 : 11:51:49
|
| I applied SP4 today (we're still on SQL Server 2000) as we experienced the same problem again. So far so good but I'm not getting too excited...I guess it's ok to see idle connections but is it normal to have over 1000? The sites aren't mega busy - maybe 10,000 unique hits a day...ThanksTrevor |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-21 : 22:41:03
|
| What's your sql edition? Standard edition can only use 2gb memory. And there is awe related bug in original sp4, need apply sp4a or post sp4 fix if you use enterprise edition. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tcrkay
Starting Member
5 Posts |
Posted - 2008-07-23 : 04:37:45
|
| Hi. I've checked and the latest release for SQL 2000 is SP4.I've noticed it generally only throws an error on dynamic SQL statements (i.e. those that are run from the page itself rather than a stored procedure.It also happens mainly at busier times of day.It's really strange, causing lots of grief. Any advice is appreciated. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-23 : 23:52:22
|
| There are post sp4 fixes. |
|
|
|
|
Connection Problems