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
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Sunday, March 25, 2012
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 ou
t?
Any help is appreciated.
Jonathan Apeldoorn,
Windkracht InternetJonathan
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 th
e
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, ther
e
> is no time to dig into what is causing it, as operation needs to be resume
d
> 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, ther
e
> is no time to dig into what is causing it, as operation needs to be resume
d
> 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
>
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 ou
t?
Any help is appreciated.
Jonathan Apeldoorn,
Windkracht InternetJonathan
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 th
e
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, ther
e
> is no time to dig into what is causing it, as operation needs to be resume
d
> 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, ther
e
> is no time to dig into what is causing it, as operation needs to be resume
d
> 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
>
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 InternetJonathan
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...
> > 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 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
>
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 InternetJonathan
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...
> > 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 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
>
Thursday, March 22, 2012
Detecting or monitoring for long-running queries.
I can't seem to find a step-by-step guide to how to detect or
continuously monitor for long-running queries that is suitable
for a comparative SQL Server novice. I know that it is possible
to monitor for such with other database products - can anyone
enlighten me as to how this is done with SQL Server? Ideally,
I'd like to snapshot running queries (preferably with query
text) at a particular instant via a script, although any help
to show what queries are running at an instant will be much
appreciated. Any ideas?
continuously monitor for long-running queries that is suitable
for a comparative SQL Server novice. I know that it is possible
to monitor for such with other database products - can anyone
enlighten me as to how this is done with SQL Server? Ideally,
I'd like to snapshot running queries (preferably with query
text) at a particular instant via a script, although any help
to show what queries are running at an instant will be much
appreciated. Any ideas?
__________________________________________________ ___________
Are you Catholic ?
http://www.CatholicEmail.com
100s of FREE email addresses -->
http://www.UltimateEmail.com
Send an Online Greeting Card http://www.UltimateEcards.comsqlserver yeahbaby (Use-Author-Address-Header@.[127.1]) writes:
> I can't seem to find a step-by-step guide to how to detect or
> continuously monitor for long-running queries that is suitable
> for a comparative SQL Server novice. I know that it is possible
> to monitor for such with other database products - can anyone
> enlighten me as to how this is done with SQL Server? Ideally,
> I'd like to snapshot running queries (preferably with query
> text) at a particular instant via a script, although any help
> to show what queries are running at an instant will be much
> appreciated. Any ideas?
On my web site there is a stored procedure, aba_lockinfo, that is not
a perfect match for your request, but which does give the information
you are asking for - and maybe information which you do not care
about. You could look at
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html, and see if
it anything for you.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Monday, March 19, 2012
details about finetuning queries
table1: In timings history
EMPID pk INTIME EFFECTIVE_DATE END_DATE
ENTERED_BY
tabl2 intiming
EMPID pk INTIME EFFECTIVE_DATE END_DATE enteredby
ta3
emptimings
ENTRYTIME pk EMPID TIMETYPE INOUT IP_ADDRESS
i want to calculate emp salary after deducting leaves and break timings
and other cuttingsddl means
CREATE TABLE TABLENAME ETC
SAMPLE DATA MEANS : insert into
--
Regards
R.D
--Knowledge gets doubled when shared
"raghu veer" wrote:
> table1: In timings history
> EMPID pk INTIME EFFECTIVE_DATE END_DATE
> ENTERED_BY
> tabl2 intiming
> EMPID pk INTIME EFFECTIVE_DATE END_DATE enteredby
> ta3
> emptimings
> ENTRYTIME pk EMPID TIMETYPE INOUT IP_ADDRESS
>
> i want to calculate emp salary after deducting leaves and break timings
> and other cuttings
>
>
EMPID pk INTIME EFFECTIVE_DATE END_DATE
ENTERED_BY
tabl2 intiming
EMPID pk INTIME EFFECTIVE_DATE END_DATE enteredby
ta3
emptimings
ENTRYTIME pk EMPID TIMETYPE INOUT IP_ADDRESS
i want to calculate emp salary after deducting leaves and break timings
and other cuttingsddl means
CREATE TABLE TABLENAME ETC
SAMPLE DATA MEANS : insert into
--
Regards
R.D
--Knowledge gets doubled when shared
"raghu veer" wrote:
> table1: In timings history
> EMPID pk INTIME EFFECTIVE_DATE END_DATE
> ENTERED_BY
> tabl2 intiming
> EMPID pk INTIME EFFECTIVE_DATE END_DATE enteredby
> ta3
> emptimings
> ENTRYTIME pk EMPID TIMETYPE INOUT IP_ADDRESS
>
> i want to calculate emp salary after deducting leaves and break timings
> and other cuttings
>
>
Tuesday, February 14, 2012
Design Choices
I have 20-30 action queries which will be executed nightly. Is it better to place them in a DTS package and call the package, or place them in a stored procedure and call the stored procedure?If you are using a Access dtabase on a web site and using an inc file for your data connections I can down load the database and crack security. As for the performance of Acess it sucks over 1,000,000,000 records.|||This is for a SQL Server database. I'm not looking for your opinion on Access|||depends on what your action queries do. If you need to move data then DTS is the way to go. If you need to work with data inside SQL server then I would faver stored procedures.
Subscribe to:
Posts (Atom)