Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Client has a dangerously open instance of SQL Server 2005. VB.NET 1.1 connects to this from another server without problem. Turning on Windows firewall with port 1433 open to subnets only seems like a quick stopgap solution.

DB seems fine, but begins to slow down over the course of an hour... As though it is losing connections form it's pool. Eventually no connections are possible from the app or other tools.

Does SQL Server require other ports to be open?

Linux guy here scratching his head.

share|improve this question
add comment

4 Answers 4

up vote 0 down vote accepted

SQL Server provides a way to monitor open connections under "SQL Server Management Studio". I would check to make sure that connections are not getting opened up and left open from the application. Because unless the application explicitly closes the connection it could be possible for it to remain open until the Garbage Collector decides to finally clean up the object and release the connection back to the pool.

Another way to check and see if this is happening is to wait until the application can no longer get connected. Once in this state if restarting the application solves this problem then it is probably an issue with the application code.

share|improve this answer
add comment

Assuming you're running SQL Server with the defaults, then 1433 should be the only port you need open.

About the slowdown part - use my Perfmon monitoring tutorial to gather statistics about the SQL Server. From there we can determine which part of the server is the bottleneck. If you want, gather the stats as shown in the article for a few minutes while the server is fast, and then again while the server is slow. There's no performance impact of gathering these statistics. Then send the files to me at brento@brentozar.com and I should be able to tell pretty quickly where the bottleneck is.

share|improve this answer
add comment

Run sp_who2 to see who is connected and what they using.

JR

share|improve this answer
    
Why was this voted down? The stored procedure sp_who2 is the first thing I'd run if I suspect excessive connections. Could you at least leave a comment saying why you marked my post down? –  John Rennie Jun 13 '09 at 11:37
add comment

When connections are no longer possible can you connect locally on the box? If so then try connecting again using Management Studio but instead of using "Share Memory" use "TCP/IP".

If the latter fails then indeed I would investigate the firewall situation. However I wouldn't be inclined to associate a firewall setting with "loss of connectivity over time".

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.