SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Not responding from 8:00 PM to 10:00 Pm


Database Not responding from 8:00 PM to 10:00 Pm

Author
Message
rollercoaster43
rollercoaster43
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 435
Dear All,

I have an Sql server 2005 server which is hosting multiple Databases.

Recently, users are complaining that the application becomes unresponsive for a particular time frame of 2 hours.

I have seen a couple of jobs that run during this time frame(which I cannot rechedule to a different time) but I do not have anything concrete to put the Blame on these jobs.

I am planning to start the trace flag to understand if there is anything getting deadlocked. Also, a server side trace to capture details of blockings for this victim database.

Is there anything else that I should try to capture for this Database to understand the reason for it being so unresponsive?

Any suggestions are most welcome.

Thanks in advance..
Ray Herring
Ray Herring
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1591 Visits: 607
Your problem description is pretty generic (my server is non responsive sometimes) and the Bad Me is tempted to reply, you probably have a performance problem to resolve Tongue

Questions that come to mind include:
1. Is it a single database that is non-responsive or is SQL totally non-responsive?
2. What do the jobs do? Index maintenance, Backups, ETL, ...?
3. Are there Non-SQL (heaven forbid!) applications such as Exchange, IIS, File Shares, etc. on the server?
4. Have you reviewed the SQL Error log and Windows Event logs to see if any entries correspond to the event?
5. Is there an application that runs a big report each day?
6. Do you see any evidence of memory pressure?
7. Does disk activity increase significantly?

I am sure there are many more questions that others might suggest but at least answering a couple of these will get you on track to either eliminating or identifying a problem source.
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8736 Visits: 3718
Try to ping and connect (RDP) to the server during the time frame when users complain the server is not responding. Try to run SSMS from the RDP session. Also run a perfmon trace to see about CPU, memory and/or disk pressure. If results from the perfmon trace show no strange behaviour, run a SQL serverside profiler trace to see what is going in on the SQL instance.

Deadlocks probably won't be the issue. In a deadlock situation only one of the processes is killed and an error is reported back to the application. This won't result in a unresponsive experience for the user.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
crmitchell
crmitchell
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1264 Visits: 1793
Definitely check you have connectivity to the server during that period.
There may be a job (SQL or OS) or a system update running on a different server which means the network may be inaccessible - firewalls and DNS servers come to mind here.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search