Determine cause of Timeouts

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
Jonathan
It depends on locks,blocks, poor written queries, memory.
It' time to start with www.sql-server-performance.com
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:3931F0E1-F6F1-4E69-938A-464D5BA944CF@.microsoft.com...
> 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
>
|||Thank you for your answer.
The Database is used in a web application (ASP).
All queries are used extensively (several times a minute, at least more than
once a week).
Server wise, it's a dual proc, with 2Gb of memory. The memory is, of course
all used up by SQL Server. CPU wise, they're max used about 60%.
I'll go have a look at www.sql-server-performance.com.
"Uri Dimant" wrote:

> Jonathan
> It depends on locks,blocks, poor written queries, memory.
> It' time to start with www.sql-server-performance.com
>
>
> "Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
> news:3931F0E1-F6F1-4E69-938A-464D5BA944CF@.microsoft.com...
> there
> resumed
> out?
>
>
|||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.
"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
>
|||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"
"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
>
sql