May 3, 2006 at 7:55 am
I have two Win2K machines on the same LAN, both with SQL Server 2000 running on them (everything has been patched up-to-date). On the first machine, anytime there is some serious querying or updates going on, it causes lots of network access (the querying/updating is being done from QA on the server itself). The problem is that there is a crazy amount of data being uploaded by the second machine, this upload pattern matches the heavy amount of downloading on the first server (where the query/data resides).
Keep in mind that ALL of the data resides on the first machine that is being queried. Nothing involving this query is on the 2nd machine.
This huge amount of uploading/downloading is totally conjesting the network bandwidth for other normal functions so the query has to be cancelled.
Any ideas?
Thanks for your help!
Joey
May 3, 2006 at 8:50 am
Are they linked servers?
Is there a trigger that moves data from one to the other?
Do you have transactional replication set up?
-SQLBill
May 3, 2006 at 8:58 am
No, they're not linked in any way, no triggers at all, and no replication is set up. These servers are completely independent. The "second" has been running for a number of years, the other is brand new with totally new databases that aren't at all related to the data on the other.
May 3, 2006 at 9:14 am
I, at one time, thought that the problem might could be solved in the network settings -- they both had the same named pipe. So, in my changes I renamed pipes so they were different, totally removed named pipes from the new server, and finally totally removed all network protocols from SQL server (including TCP/IP). Then, I ran the query -- no change. Bandwidth usage shot up.
May 3, 2006 at 10:26 am
Do you attach by a specific name via QA
If so check in the client configuration tool and make sure the name you are using isn't defined pointing at the other server. Also, check your host file to be sure not there as well.
If neither are the case stop the SQL Server on the server you are running QA and connect again. If works then somewhere you are causing yourself to be directed to the other server instead.
May 4, 2006 at 11:30 am
First, have you checked your NIC card settings ? Particularly 'speed' and 'mode'. Not having the NIC card match the switch port can cause this type of issue. Since it's a server you are probably looking at setting of '100 Mb' and 'Full Duplex' as opposed to 'auto' and 'auto'. Second, check the version of the NIC drivers. Windows Update can get you the most recent version. However, if this is an IBM E-Series server, DO NO use Windows Update.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 4, 2006 at 12:19 pm
I would run Profiler on the second machine during the upload and see if any of the activity goes towards the first machine. It could be not the SQL Server something but NTFS something, depending on the application running. Also, do check your queries. I had a case once when a user ran a query without Where from her workstation using a third-party report tool on SQL server going towards Oracle database. First, Oracle dba was reporting her server's heavy activity, then result set was returned to the query tool (with a lot of network activity) so SQL server machine ran out of memory then the whole resultset was trying to make its way back to the user's workstation.
Regards,Yelena Varsha
May 4, 2006 at 2:15 pm
The NIC card idea is a good one because there are other weird problems going on with this server (for example, My Network Places can't see any of the other machines on the network). I'll check those settings out and see if they change things.
Also, the Profiler is something I've tried, but the info is sort of sparse to me and wasn't usable. The idea of memory being an issue is also one I'll check. This is a pretty large database and the queries are pretty complicated.
Finally, the basic issue of having the upload/download between the two machines seems to have been solved by using a different username/password - the username we had been using was also on the "second" machine.
So, though that initial problem seems to be solved, we're still getting lots of *network* types of issues, lost SQL server connections, etc.
May 4, 2006 at 2:37 pm
If you solved by changing the username/password you were using to something not on the second machine it sounds to me like you have create a bad routing somewhere that may be talking to the second machine first.
May 4, 2006 at 2:54 pm
You might also look into your DNS and WINS settings on the network cards for both computers. Verify the subnet masks. Talk to your network people and make sure that both servers are ergistered properly in DNS.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply