Click here to monitor SSC
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
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 434
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
SSC Eights!
SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)

Group: General Forum Members
Points: 815 Visits: 567
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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
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
SSC-Addicted
SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)

Group: General Forum Members
Points: 451 Visits: 1715
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