• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

SQL Server Freezing Up

Hello Experts,

I have a Windows 2003 Server running an ASP.Net application against a SQL Server Database. Each client on the system has its own instance of the database. We were recently the victim of several attacks including several aimed at SQL Server itself. We have tightened our defenses with firewall setting that deny everything except manually entered IP address. Since we have changed our security policy SQL Server has started to freeze up under normal traffic conditions and requires a reboot of the server to clear it and then its fine again for about a day then the same thing. Our app times out when making requests to SQL.

The SQL Logs reflect a large number of rollbacks/rollforward messages. These are very small databases and the Trans Logs are less than 5 Mbs.

I need help please.

Jack
0
JackW9653
Asked:
JackW9653
  • 7
  • 6
1 Solution
 
ptjcbCommented:
Are you blocking  TCP port 1433 and UDP port 1434?
0
 
JackW9653Author Commented:
Don't believe so as the system functioned perfectly until the freeze. So if those ports were blocked I wouldnt be able to connect at all.
0
 
ptjcbCommented:
The easiest way to check is to ping them... Is this freeze intermittent? Does it happen at a particular time? What other processes are running (backups, etc)?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
JackW9653Author Commented:
I checked and could telnet into port 1433 fine, but failed to connect on 1434. I used the command telnet 1.1.1.1 1434 to test the port. The freeze is intermittent, but no particular time and no intrusive process that i can find in the logs.
0
 
ptjcbCommented:
Do you have any processes running on the server when it freezes? We had an issue where conflicting jobs (SQL and Windows) would lock up SQL.
0
 
JackW9653Author Commented:
No unusual processes that i am aware. I know there is a sp that can check that - do you know which one that is?
0
 
ptjcbCommented:
Not for the server that I know of - you can use Profiler to watch what is happening with SQL, but unless the task is explicitly made to write to a log you would have to check. To find our error I created a script that returned the times when jobs ran and if they overlapped. I then gave that to the Server Admins and they checked their processes.

An intermittent error is harder to track. If you had profiler running you could see what was happening in SQL Server (at least eliminating something, always a good thing). The issue is that profiler will add to the overhead for your system and could slow it down during peak times.
0
 
JackW9653Author Commented:
Ive started Profiler and I am getting a ton of Locks:Acquired by the sa. Then an entry into Transaction Log.
0
 
ptjcbCommented:
That's a good question - I am sure that you have looked at the event logs on the server along with the SQL Server logs (don't forget the SQLAgent also writes to a log). Do they say anything?
0
 
JackW9653Author Commented:
None of the log files tell me anything is unusual and currently the system is running fine - i may have to wait till it freezes again to determine the actual cause. I wonder if all of the Locks from the sa were for Profiler?
0
 
ptjcbCommented:
Yes - that is the problem with an intermittent freeze - you have to sit and watch. From your answers there does not seem to be any obvious trigger.
0
 
JackW9653Author Commented:
Thanks for the help ptjcb - I'm going to close this question and give you the points. But if it happens again - I will be posting again, so please watch for it.
0
 
ptjcbCommented:
No problem - with profiler running and your checking logs and maybe running perfmon at the time it hangs we will have better information and a better shot at finding out why.

This link will give you a good start to check: http://vyaskn.tripod.com/analyzing_profiler_output.htm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now