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

  • 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..

  • 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 😛

    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.

  • 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’! **
  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply