Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Tracing SQL Server 2000 Connections

  
   Database Forums (Home) -> Connect RSS
Next:  Clustering Newbie - SAN Advice  
Author Message
Dougie Brown

External


Since: Nov 02, 2007
Posts: 3



(Msg. 1) Posted: Fri Nov 02, 2007 7:44 am
Post subject: Tracing SQL Server 2000 Connections
Archived from groups: microsoft>public>sqlserver>connect (more info?)
Hi

We're experiencing connection timeouts when the C drive of our SQL Server is
backed up to tape. Backup Exec and native Windows Backup have both
highlighted/caused the connection timeouts which last for around 90 seconds
at the end of the backup. It would appear that by excluding System32 from
the backup eases the situation.

In order to troubleshoot this further I'd like to trace the traffic from the
network all the way into SQL Server. We've placed a sniffer before the SQL
Server and have verified that the requests are getting through, so it's not a
network issue.

I can use NetMon to see the traffic on the server and I can use SQL Profiler
to trace actual logins. The problem is during this 90 second interval, I can
see the connection request in the sniffer traffic but nothing gets logged by
SQL Profiler.

So is there anything I can use to trace the calls from the tcp/ip stack all
the way into SQL Server?

Cheers

Doug

 >> Stay informed about: Tracing SQL Server 2000 Connections 
Back to top
Login to vote
Charles Wang[MSFT]

External


Since: Jul 17, 2006
Posts: 274



(Msg. 2) Posted: Sun Nov 04, 2007 11:56 pm
Post subject: RE: Tracing SQL Server 2000 Connections [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Hi Doug,
I understand that when you tried to back up your whole C drive to your
tape, both Backup Exec and native Windows NT Backup failed with a
connection timeout. If you exclude System32 from the backup, this issue did
not appear.
If I have misunderstood, please let me know.

I think that NetMon is a very good tool to trace the calls from the TCP/IP
stack all the way into SQL Server. You may check which TCP port your SQL
Server is listening on from svrnetcn.exe (SQL 2000) or SQL Server
Configuration Manager (2005), after that you can filter the trace according
to the TCP port. However this depends on your BACKUP EXEC program using
TCP/IP protocol to connect to your SQL Server. For Windows NTBackup, it
could not backup files which are open, so it could not backup SQL Server
database files which are in use.

From your description, you mentioned that nothing got logged by SQL
Profiler, so I would like to check with you whether or not your BACKUP EXEC
utility had a valid SQL login or enough permission to log on your SQL
Server. Also, did you add "Audit Login Failed" event for your SQL Profiler
trace file?

I recommend that you check your Windows Event logs to see if there are any
error logs and post them here for further research. Additionally to narrow
down this issue to see if it is caused by SQL Server, could you please just
backup your SQL Server folder to see if it can succeed?

Thanks for using Microsoft MSDN Managed Newsgroup. If you have any
questions or concerns, please feel free to let me know. Have a nice day!


Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 >> Stay informed about: Tracing SQL Server 2000 Connections 
Back to top
Login to vote
Dougie Brown

External


Since: Nov 02, 2007
Posts: 3



(Msg. 3) Posted: Mon Nov 05, 2007 7:59 am
Post subject: RE: Tracing SQL Server 2000 Connections [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Hi Charles

Thanks for your quick response! Sorry there's a few things that I need to
clarify!

Backup Exec (Veritas) and Windows Backup don't fail, they succeed but during
the last minute of their backup connections to SQL Server from other
applications time out.

In my mind these applications are either the direct cause of the lost
connections or are stressing something and hence causing the lost connections.

I'm trying to get NetMon installed on the test cluster to trace the traffic
actually on the server but I've not been successful yet, different teams need
to be involved. Also I believe a support call has been opened with Microsoft
so I think that will take precedence over the direction of this investigation!

Thanks for your help so far, if I manage to get NetMon installed soon then I
will post a reply!

Cheers

Doug

"Charles Wang[MSFT]" wrote:

> Hi Doug,
> I understand that when you tried to back up your whole C drive to your
> tape, both Backup Exec and native Windows NT Backup failed with a
> connection timeout. If you exclude System32 from the backup, this issue did
> not appear.
> If I have misunderstood, please let me know.
>
> I think that NetMon is a very good tool to trace the calls from the TCP/IP
> stack all the way into SQL Server. You may check which TCP port your SQL
> Server is listening on from svrnetcn.exe (SQL 2000) or SQL Server
> Configuration Manager (2005), after that you can filter the trace according
> to the TCP port. However this depends on your BACKUP EXEC program using
> TCP/IP protocol to connect to your SQL Server. For Windows NTBackup, it
> could not backup files which are open, so it could not backup SQL Server
> database files which are in use.
>
> From your description, you mentioned that nothing got logged by SQL
> Profiler, so I would like to check with you whether or not your BACKUP EXEC
> utility had a valid SQL login or enough permission to log on your SQL
> Server. Also, did you add "Audit Login Failed" event for your SQL Profiler
> trace file?
>
> I recommend that you check your Windows Event logs to see if there are any
> error logs and post them here for further research. Additionally to narrow
> down this issue to see if it is caused by SQL Server, could you please just
> backup your SQL Server folder to see if it can succeed?
>
> Thanks for using Microsoft MSDN Managed Newsgroup. If you have any
> questions or concerns, please feel free to let me know. Have a nice day!
>
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
 >> Stay informed about: Tracing SQL Server 2000 Connections 
Back to top
Login to vote
Charles Wang[MSFT]

External


Since: Jul 17, 2006
Posts: 274



(Msg. 4) Posted: Tue Nov 06, 2007 2:57 am
Post subject: RE: Tracing SQL Server 2000 Connections [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Hi Doug,
Thanks for your response.

Since the fact was that the backup utility breaks the existing connections
from other application to your SQL Server, I am afraid that Netmon may not
be helpful here. I recommend that you check your SQL Server error logs to
see if your SQL Server service was stopped and then started during the
process. Every time your SQL Server is restarted, a new error log will be
generated. By default, the error logs are located in the folder C:\Program
Files\Microsoft SQL Server\MSSQL(.n)\MSSQL\LOG.

Also please use Perfmon.exe to monitor your SQL Server to see if there is
any performance issue when you run your backup utility. You may refer to
the CPU bottlenecks and I/O bottlenecks in this article:
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
If there is performance pressure, the existing connections may be
disconnected.

I am also trying to consult the product team to see if it is possible for
these backup utilities to break existing SQL connections. If there is any
response, I will let you know.

If you have any other questions or concerns, please feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 >> Stay informed about: Tracing SQL Server 2000 Connections 
Back to top
Login to vote
Dougie Brown

External


Since: Nov 02, 2007
Posts: 3



(Msg. 5) Posted: Tue Nov 06, 2007 2:57 am
Post subject: RE: Tracing SQL Server 2000 Connections [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Hi Charles

Existing connections are fine although I think they do experience a
significant delay in obtaining their results.

It's new connections that are essentially refused. It would appear that SQL
Server doesn't see the request even though we see the request on the network
via a sniffer.

This only occurs during the last 90 seconds or so of the backup where any
attempt to connect will result in a timeout error. Also this has been
observed on other SQL Server databases that are hosted on different physical
servers.

I don't think there are any performance issues as these stats are already
collected at regular intervals and nothing out of the ordinary occurs!

So is there no way of tracing a connection request all the way from the wire
(TCP/IP) into SQL Server?

Ideally I'd like to see where it breaks down, the logon audit in SQL
Profiler is too high level as we don't see anything and the trace from the
sniffer is too low level. Is there anything that would allow me to trace the
middle ground?

Thanks for you ongoing help.

Cheers

Doug



"Charles Wang[MSFT]" wrote:

> Hi Doug,
> Thanks for your response.
>
> Since the fact was that the backup utility breaks the existing connections
> from other application to your SQL Server, I am afraid that Netmon may not
> be helpful here. I recommend that you check your SQL Server error logs to
> see if your SQL Server service was stopped and then started during the
> process. Every time your SQL Server is restarted, a new error log will be
> generated. By default, the error logs are located in the folder C:\Program
> Files\Microsoft SQL Server\MSSQL(.n)\MSSQL\LOG.
>
> Also please use Perfmon.exe to monitor your SQL Server to see if there is
> any performance issue when you run your backup utility. You may refer to
> the CPU bottlenecks and I/O bottlenecks in this article:
> Troubleshooting Performance Problems in SQL Server 2005
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
> If there is performance pressure, the existing connections may be
> disconnected.
>
> I am also trying to consult the product team to see if it is possible for
> these backup utilities to break existing SQL connections. If there is any
> response, I will let you know.
>
> If you have any other questions or concerns, please feel free to let me
> know.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================
>
>
>
>
>
>
>
>
>
>
>
 >> Stay informed about: Tracing SQL Server 2000 Connections 
Back to top
Login to vote
Charles Wang[MSFT]

External


Since: Jul 17, 2006
Posts: 274



(Msg. 6) Posted: Thu Nov 08, 2007 3:57 am
Post subject: RE: Tracing SQL Server 2000 Connections [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Hi Doug,
Thanks for your response.

In TCP/IP network model, there is no middle ground between Transport layer
and Application Layer. You said, "So is there no way of tracing a
connection request all the way from the wire (TCP/IP) into SQL Server?",
Netmon can do this.

Though Netmon capture all the network loads, you can filter the traces by
the destination TCP port. For example, if your SQL Server is listening on
1433, you can just filter the trace with the destination TCP port = 1433.
After the filter, you get all the requests all the way into your SQL Server.

To track the details, please ensure that all of your clients use TCP/IP to
connect to your SQL Server. You may only enable TCP/IP protocol on your
server side via Start -> Microsoft SQL Server -> Server Network Utility.
After you do this, you need to restart your SQL Server. You can also check
which TCP port your SQL Server is using via Server Network Utility (Click
Properties when you select TCP/IP).

If there is no requests found in the trace, please check if you can access
the TCP port from your clients via telnet command: telnet <servername> <TCP
port number>

Also you can manually test the connection via osql command:
[SQL Authentication]
osql -S <servername>\<instancename> -U <username> -P <password>
[Windows Authentication]
osql -S <servername>\<instancename> -E

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 >> Stay informed about: Tracing SQL Server 2000 Connections 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
SQL Server 2000 linked server problem - This is SQL Server 2000 sp3a. I have linked servers that have been working for a fairly long time. If I am on the server (TermSvc), I cann connect and test through the linked server successfully. If I am initiating this from any workstation or..

Authentification problem with SQL Server 2000 - Hello ! We use SQL Server 2000 (standard edition) under Windows 2003 Server. Due to a hardware failure, we have fully set up the server after having formating the hard drives. After having installed Windows Server 2003, we have installed SQL Server..

Problems connecting to SQL 2000 server - We're having problems connecting to a SQL 2000 server from a Windows 2000 Professional SP 4 platform. When we try to connect we get an error message, first advising SQL state 01000, SQL error code 16061, then SQL state 08001, SQL error code 11. This...

Connecting to the SQL server 2000 with Windows Authenticat.. -

Oracle Solaris connectivity from SQL Server 2000 - Hi, We have data in SQL Server 2000 database and we need to transfer this to Oracle 9.2 database in SOLARIS environment. Oracle windows version alone can connect to SQL Server and since we are using Oracle in Solaris, we cannot establish a connection...
   Database Forums (Home) -> Connect All times are: Pacific Time (US & Canada)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]