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 experiencing. Show all posts
Showing posts with label experiencing. 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 Installed IIS - installing SQL 2005 Standard
I've seen lots of posts but few if any solutions to this apparently common problem.
I'm experiencing a variant - installing SQL 2005 (RTM) on a brand new machine with Server 2003 Standard Ed. SP1, the setup program frequently pauses on steps identified as "Detecting Installed IIS". Setup eventually moves on, but it's taking ages to finish the install.
I've installed this exact version of several identical machines, but this is the first where I've encountered this behavior.
Does anyone have any input on what's really happening here and what to do about it?I guess no one really knows... :)
In my case, I believe that the cause was a DNS issue that caused all name resolution requests to time out. Others have reported issues related to firewalls or antivirus software.|||
Thanks for posting your solution, Carl. What was the DNS issue you were hitting? Did you have to change some settings to get it working? And approximately how long was the slow install? Hours? This issue has appeared a few times on here and I don't think we have a good answer yet other than DNS/firewall/anti-virus issues.
Thanks,
Sam Lester (MSFT)
Now this is stressing the way-back machine!
The slow install was, as I recall, something on the order of 2-5 minutes each time something tried to detect IIS (and it happens several times during the install).
If I recall, the solution was to configure TCP/IP on the install machine to use the local domain controller for DNS rather than my upstream providers' DNS server.
Detecting Installed IIS - installing SQL 2005 Standard
I've seen lots of posts but few if any solutions to this apparently common problem.
I'm experiencing a variant - installing SQL 2005 (RTM) on a brand new machine with Server 2003 Standard Ed. SP1, the setup program frequently pauses on steps identified as "Detecting Installed IIS". Setup eventually moves on, but it's taking ages to finish the install.
I've installed this exact version of several identical machines, but this is the first where I've encountered this behavior.
Does anyone have any input on what's really happening here and what to do about it?I guess no one really knows... :)
In my case, I believe that the cause was a DNS issue that caused all name resolution requests to time out. Others have reported issues related to firewalls or antivirus software.|||
Thanks for posting your solution, Carl. What was the DNS issue you were hitting? Did you have to change some settings to get it working? And approximately how long was the slow install? Hours? This issue has appeared a few times on here and I don't think we have a good answer yet other than DNS/firewall/anti-virus issues.
Thanks,
Sam Lester (MSFT)
Now this is stressing the way-back machine!
The slow install was, as I recall, something on the order of 2-5 minutes each time something tried to detect IIS (and it happens several times during the install).
If I recall, the solution was to configure TCP/IP on the install machine to use the local domain controller for DNS rather than my upstream providers' DNS server.
Detecting Installed IIS - installing SQL 2005 Standard
I've seen lots of posts but few if any solutions to this apparently common problem.
I'm experiencing a variant - installing SQL 2005 (RTM) on a brand new machine with Server 2003 Standard Ed. SP1, the setup program frequently pauses on steps identified as "Detecting Installed IIS". Setup eventually moves on, but it's taking ages to finish the install.
I've installed this exact version of several identical machines, but this is the first where I've encountered this behavior.
Does anyone have any input on what's really happening here and what to do about it?I guess no one really knows... :)
In my case, I believe that the cause was a DNS issue that caused all name resolution requests to time out. Others have reported issues related to firewalls or antivirus software.|||
Thanks for posting your solution, Carl. What was the DNS issue you were hitting? Did you have to change some settings to get it working? And approximately how long was the slow install? Hours? This issue has appeared a few times on here and I don't think we have a good answer yet other than DNS/firewall/anti-virus issues.
Thanks,
Sam Lester (MSFT)
Now this is stressing the way-back machine!
The slow install was, as I recall, something on the order of 2-5 minutes each time something tried to detect IIS (and it happens several times during the install).
If I recall, the solution was to configure TCP/IP on the install machine to use the local domain controller for DNS rather than my upstream providers' DNS server.
Subscribe to:
Posts (Atom)