Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Database Not responding from 8:00 PM to 10:00 Pm Expand / Collapse
Posted Monday, September 16, 2013 1:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, January 4, 2016 10:41 PM
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..
Post #1494953
Posted Wednesday, October 2, 2013 3:34 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, November 29, 2016 10:27 AM
Points: 762, Visits: 554
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 :P

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.
Post #1500942
Posted Thursday, October 3, 2013 12:53 AM



Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 2,997, Visits: 3,585
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’! **
Post #1501011
Posted Thursday, October 3, 2013 6:12 AM


Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 437, Visits: 1,671
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.
Post #1501143
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse