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
JackW9653Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ptjcbCommented:
Are you blocking  TCP port 1433 and UDP port 1434?
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.
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)?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.
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.
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?
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JackW9653Author Commented:
Ive started Profiler and I am getting a ton of Locks:Acquired by the sa. Then an entry into Transaction Log.
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?
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?
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.
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.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.