?
Solved

SQL Server Freezing Up

Posted on 2006-04-20
13
Medium Priority
?
246 Views
Last Modified: 2008-03-10
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
Comment
Question by:JackW9653
  • 7
  • 6
13 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 16508694
Are you blocking  TCP port 1433 and UDP port 1434?
0
 

Author Comment

by:JackW9653
ID: 16508765
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
 
LVL 27

Expert Comment

by:ptjcb
ID: 16508789
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:JackW9653
ID: 16508947
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
 
LVL 27

Expert Comment

by:ptjcb
ID: 16508982
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
 

Author Comment

by:JackW9653
ID: 16509048
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
 
LVL 27

Accepted Solution

by:
ptjcb earned 2000 total points
ID: 16509166
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
 

Author Comment

by:JackW9653
ID: 16509252
Ive started Profiler and I am getting a ton of Locks:Acquired by the sa. Then an entry into Transaction Log.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16509322
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
 

Author Comment

by:JackW9653
ID: 16509451
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
 
LVL 27

Expert Comment

by:ptjcb
ID: 16509549
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
 

Author Comment

by:JackW9653
ID: 16509919
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
 
LVL 27

Expert Comment

by:ptjcb
ID: 16509976
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question