Determine cause of Timeouts

15 Mar 2005 10:45 AM

Jonathan

Recently we have been experiencing a problem with SQL Server 2000, that is
causing all queries to receive a timeout.
This happens intermittently, but about once a week.
Unfortunately, we can't seem to determine the cause of the problem.

What we've determined/noticed so far:
* The database and transaction log is backed up every night
* The database is about 300Mb in size
* The transaction log is normally about 600Mb in size
* Before the timeouts occur, and before the TL is backed up, the log grows
to 3-9Gb in size
* The TL is normally shrinked after each backup, but not when the problem
occurs.
* After a restart of SQL Server or reboot of the server, everything runs
fine again

After reading through numerous KB articles and posts, it appears to be a
transaction keeping locks on one or more tables, causing the timeouts.
As this is a heavily used production-server, once the timeouts occur, there
is no time to dig into what is causing it, as operation needs to be resumed
asap.

Is there a way to determine what queries/triggers/sp's or transactions are
the cause in the first place beforehand?
Or is it possible to read through the transaction logs themselves to find out?

Any help is appreciated.

Jonathan Apeldoorn,

Windkracht Internet

25 Mar 2005 6:31 PM

Jeff R

Jonathan -

If the transaction log is growing when you encounter the problem, I would
start by looking for a run-away transaction that is causing changes to the
database. In the enterprise manager check the active threads to see what the
last issued SQL statement was while the problem is occurring, this may help
quickly identify the problem. Also, if the occurrence of the problem is
relatively consistent in terms of day/time starting a trace may also be
helpful. You can go through the trace log to identify the offending
transaction. If the query "times out" you may also want to check the SQL
server log there may information there to help you out.

Show quoteHide quote

"Jonathan" wrote:

> Recently we have been experiencing a problem with SQL Server 2000, that is
> causing all queries to receive a timeout.
> This happens intermittently, but about once a week.
> Unfortunately, we can't seem to determine the cause of the problem.
>
> What we've determined/noticed so far:
> * The database and transaction log is backed up every night
> * The database is about 300Mb in size
> * The transaction log is normally about 600Mb in size
> * Before the timeouts occur, and before the TL is backed up, the log grows
> to 3-9Gb in size
> * The TL is normally shrinked after each backup, but not when the problem
> occurs.
> * After a restart of SQL Server or reboot of the server, everything runs
> fine again
>
> After reading through numerous KB articles and posts, it appears to be a
> transaction keeping locks on one or more tables, causing the timeouts.
> As this is a heavily used production-server, once the timeouts occur, there
> is no time to dig into what is causing it, as operation needs to be resumed
> asap.
>
> Is there a way to determine what queries/triggers/sp's or transactions are
> the cause in the first place beforehand?
> Or is it possible to read through the transaction logs themselves to find out?
>
> Any help is appreciated.
>
> Jonathan Apeldoorn,
>
> Windkracht Internet
>

25 Mar 2005 6:37 PM

Jeff Robinson

Jonathan -

If the transaction log is growing at that rate when the problem occurs it
may be some type of run-away mass update. (insert/update/delete). As the
log shouldn't grow with query-only transactions.

A couple of suggestions that may help.

1. Check the active SQL threads using EM while the problem is occuring, you
may be able to see the SQL command that was issued that is causing the
problem.

2. If the problem occurs with some regularity, running a Trace may help
identify the problem, after recovery you can go through the trace to
identify the offending transaction.

3. If there is an actual "time-out", check the SQL log as it may contain
information on the command that "timed-out"

Show quoteHide quote

"Jonathan" wrote:

> Recently we have been experiencing a problem with SQL Server 2000, that is
> causing all queries to receive a timeout.
> This happens intermittently, but about once a week.
> Unfortunately, we can't seem to determine the cause of the problem.
>
> What we've determined/noticed so far:
> * The database and transaction log is backed up every night
> * The database is about 300Mb in size
> * The transaction log is normally about 600Mb in size
> * Before the timeouts occur, and before the TL is backed up, the log grows
> to 3-9Gb in size
> * The TL is normally shrinked after each backup, but not when the problem
> occurs.
> * After a restart of SQL Server or reboot of the server, everything runs
> fine again
>
> After reading through numerous KB articles and posts, it appears to be a
> transaction keeping locks on one or more tables, causing the timeouts.
> As this is a heavily used production-server, once the timeouts occur, there
> is no time to dig into what is causing it, as operation needs to be resumed
> asap.
>
> Is there a way to determine what queries/triggers/sp's or transactions are
> the cause in the first place beforehand?
> Or is it possible to read through the transaction logs themselves to find out?
>
> Any help is appreciated.
>
> Jonathan Apeldoorn,
>
> Windkracht Internet
>