Re: Server stops responding

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@xxxxxxxxx> wrote in message news:5E9E21E3-B1B8-4E1B-AC07-6279B4A53C17@xxxxxxxxxxxxxxxx
Thanks for your reply. I have added all these performance counters and here
are the readings :

Memory: Available MBytes = 1294
Memory: Page Faults\sec > 250 (Sometimes)
MSSQL$<your_instance_name>: Buffer Manager: Buffer cache hit ratio = 99.3
MSSQL$<your_instance_name>: Buffer Manager: Page life expectancy = 3091
MSSQL$<your_instance_name>: General Statistics: User Connections = 55
Network Interfce: Bytes Received\sec = 2000 to 10000
Network Interfce: Bytes Sent\sec = 2000 to 10000
Network Interfce: Output Queue Length = 0
Physical Disk: % Disk Time (seperately for your OS and database disks, not
total) = OS : 5 to 20 | Database : 0 to 1
Physical Disk: Disk Read Bytes\sec , OS: 0 | Database : sometimes greater than 90,000
Physical Disk: Disk Write Bytes\sec, OS: 0 to 5000 (Sometimes 25,000) | Database : 0 to 115000
Physical Disk: Avg. Disk Queue Length = almost 0 and less than 1
Processor: % Processor Time = 0 to 60
System: Processor Queue Length


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:

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@xxxxxxxxx> wrote in message
news:B607126D-F7A9-46E8-9EFF-74247996223D@xxxxxxxxxxxxxxxx
> 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:
>
>> 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@xxxxxxxxx> wrote in message
>> news:097CE843-9724-4F95-9990-CD5AAD53D135@xxxxxxxxxxxxxxxx
>> > 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.
>> >
>>


.

Relevant Pages

  • Re: % Disk Time
    ... Avg and current Disk queues instead. ... > I have separate drives each SQL Server database: ... > then your SQL Server ... >> Is not a very reliable indicator of an IO bottleneck. ...
    (microsoft.public.sqlserver.server)
  • Re: 2 million records
    ... first point, its not a datawarehousing question, but a general SQL Server ... question & Web Developper question. ... second is not relevant to fine tune your database. ... Look at the avg disk queue length perf counter, ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: SQL Server Issues ::: ( how to avoid this issues )
    ... The file sizehave all the info you need for DISK REINIT. ... Log pages where SQL Server expects data pages is automatically handled over time. ... Or just transfer the stuff into a new database. ... >>> You guys are thinking just in the higher level of IT security policies... ...
    (microsoft.public.sqlserver.server)
  • Re: Create database fails because of lack of disk space...
    ... I've never seen things like that so just shooting in the ... Is it possible that SQL server gets really funky and doesn't read OS ... causing SQL server to still think the disk drive is full? ... > CREATE DATABASE fails ("CREATE DATABASE failed. ...
    (microsoft.public.sqlserver.server)
  • Re: Determine Disk Block Size ?
    ... As always, thanks Andrew. ... Most of the documentation for 2000 is wrong in that it may state you need multiple files to spawn multiple threads. ... >> The MS SQL Server Operations Manual makes a recommendation of using 64-KB>> cluster sizes. ... We have done so with a dramatic total disk I/O>> throughput. ...
    (microsoft.public.sqlserver.server)
Loading