Hi All,

I am running SQL Server 2005 SP2 (Without SP1) on Windows 2003 Machine. I am
facing a very strange problem. Sometimes the database stops responding, In
this situation no user from my web application can connect to database. But
If I go to server and open connection by management studio it works. Secondly
I have also observed that the server becomes responsless for 3 to 4 minutes
and aftet that it again comes on normal behaviour. During all this it never
writes any errors in log entries. Can anyone tell me the reason of it and how
it can be cured !!

Thanks in advance.

Hello,


That's for sure you have problems with some services or something on your
Windows Server System or hardware I think.

Maybe it's the reason why SQL Server doesn't response sometimes?

You said there is nothing in the error logs, what error logs? Ones in Event
Viewer, or in SQL Server? Your should check both of them.

It seems it's nothing to do with SQL Server at first sight.

How about adding some counters to the System Monitor and check to see if
there is a bottleneck with one of your hardware components?


--
Ekrem Önsoy



"Altaf Al-Amin Najwani" <altaf.alamin (AT) gmail (DOT) com> wrote

Thanks Ekrem!

I am checking event viewer as well as sql server logs, both are silent about
this event. Secondly can you please recommend me any performance counters i
shall look for ? CPU runs at 35% utilization level .

Thanks in advance.

"Ekrem Önsoy" wrote:

Ok, you may want to follow the tracks of the following counters to see the
situation of Memory, Physical Disks, Network Card and CPU generally...

Memory: Available MBytes
Memory: Page Faults\sec
MSSQL$<your_instance_name>: Buffer Manager: Buffer cache hit ratio
MSSQL$<your_instance_name>: Buffer Manager: Page life expectancy
MSSQL$<your_instance_name>: General Statistics: User Connections
Network Interfce: Bytes Received\sec
Network Interfce: Bytes Sent\sec
Network Interfce: Output Queue Length
Physical Disk: % Disk Time (seperately for your OS and database disks, not
total)
Physical Disk: Disk Read Bytes\sec
Physical Disk: Disk Write Bytes\sec
Physical Disk: Avg. Disk Queue Length
Processor: % Processor Time
System: Processor Queue Length

--
Ekrem Önsoy


"Altaf Al-Amin Najwani" <altaf.alamin (AT) gmail (DOT) com> wrote

Thanks for your reply. I have added all these performance counters and here
are the readings :


I would appreciate if you can have a look at the above readings and tell me
where is the problem.

Thanks in advance.


"Ekrem Önsoy" wrote:

Even if it would be good to see "System: Processor Queue Length" too, other
results are quite good. Only "Page Faults" looks a bit high but it must be
something else or you may want to dig it.

Besides, it's very important when did you analyse these hardwares and how
long did it take.

You may be experiencing problems with CPU? because %60 is not a bad resuld
nor very good. Maybe in peak hours this value jumps to much higher? And that
may be resulting with pauses? This is just a guess.

You may want to have a look at the processes that runs from the Task Manager
and see if there is a cpu\ram consuming process running. If so, track it to
see what it is about exactly.

--
Ekrem Önsoy



"Altaf Al-Amin Najwani" <altaf.alamin (AT) gmail (DOT) com> wrote

Also, you may want to track your database with SQL Server Profiler to see if
some processes takes longer then they should take.
http://msdn2.microsoft.com/en-us/library/ms187929.aspx

You may also want to check out the Activity Monitor if there are locks or
deadlocks. Reason of the pauses could be locks or deadlocks as well.
http://msdn2.microsoft.com/en-us/library/ms175518.aspx


On the other hand, first, you said that your server experience this pausing
problem too. So, all this pausing\performance problems that you meet in SQL
Server may be result from the server itself. I think you must solve your
server problems first.

--
Ekrem Önsoy



"Altaf Al-Amin Najwani" <altaf.alamin (AT) gmail (DOT) com> wrote