About 2 times a week our SQL Server 2000 system will go into "firehose
mode" and we aren't able to run certain stored procedures. It seems
the ones that are affected are any procedures accessing linked
servers. The only way we can solve the problem is by restarting the
sql service on the server. This is starting to become a drag on our
business because all data processing stops when restart SQL. Has
anybody else encountered this issue with your systems?
Thanks,
JK
PS - We are not using Enterprise Manager so that is not the cause of
the problem. We are suspicous of the SQL Object Browser in Query
Analyzer and wonder if that uses the same "firehose" cursor as EM.Hello JK,
These links will help you to work on your problem.
PRB: SQL Enterprise Manager Returns "Cannot Start
Transaction While in Firehose Mode" Error
http://www.support.microsoft.com/?id=237398
FIX: Cursor Overhead Higher on SQL Server 7.0 for Small
Result Sets
http://support.microsoft.com/support/kb/articles/Q197/8/00.
ASP
Good Luck!
-SQLVarad (MCDBA-1999,MCSE-1999)
>--Original Message--
>About 2 times a week our SQL Server 2000 system will go
into "firehose
>mode" and we aren't able to run certain stored
procedures. It seems
>the ones that are affected are any procedures accessing
linked
>servers. The only way we can solve the problem is by
restarting the
>sql service on the server. This is starting to become a
drag on our
>business because all data processing stops when restart
SQL. Has
>anybody else encountered this issue with your systems?
>Thanks,
>JK
>PS - We are not using Enterprise Manager so that is not
the cause of
>the problem. We are suspicous of the SQL Object Browser
in Query
>Analyzer and wonder if that uses the same "firehose"
cursor as EM.
>.
>|||One of those articles references Enterprise Manager, which we are not
using. The other one talks about SQL Server 7.0, which we are not
using.
Thanks for your help.
"SQLVarad" <SQLVarad@.hotmail.com> wrote in message news:<074101c3ad67$6182baa0$a101280a@.phx.gbl>...
> Hello JK,
> These links will help you to work on your problem.
> PRB: SQL Enterprise Manager Returns "Cannot Start
> Transaction While in Firehose Mode" Error
> http://www.support.microsoft.com/?id=237398
> FIX: Cursor Overhead Higher on SQL Server 7.0 for Small
> Result Sets
> http://support.microsoft.com/support/kb/articles/Q197/8/00.
> ASP
> Good Luck!
> -SQLVarad (MCDBA-1999,MCSE-1999)
> >--Original Message--
> >About 2 times a week our SQL Server 2000 system will go
> into "firehose
> >mode" and we aren't able to run certain stored
> procedures. It seems
> >the ones that are affected are any procedures accessing
> linked
> >servers. The only way we can solve the problem is by
> restarting the
> >sql service on the server. This is starting to become a
> drag on our
> >business because all data processing stops when restart
> SQL. Has
> >anybody else encountered this issue with your systems?
> >
> >Thanks,
> >
> >JK
> >
> >PS - We are not using Enterprise Manager so that is not
> the cause of
> >the problem. We are suspicous of the SQL Object Browser
> in Query
> >Analyzer and wonder if that uses the same "firehose"
> cursor as EM.
> >.
> >
2012年2月19日星期日
Firehose mode is disrupting processes
Over the last two months our SQL 2000 Server has been going into
firehose mode very frequently. Probably about 2 times a weeek. This
is a major annoyance since we have to restart SQL every time this
error comes up. Sometimes we can get into Enterprise Manager and view
that a spid is blocking another spid from doing it's work. Sometimes
we unable to view the spid and Enterprise Manager locks up. It's very
frustrating and wonder if anybody else has came across with their
servers?
Thanks,
JKThere are a couple of Kbase articles on Firehose mode :-
http://support.microsoft.com/default.aspx?scid=kb;en-us;286199
and 237398
--
HTH
Ryan Waight, MCDBA, MCSE
"Josh King" <joshk@.geoaccess.com> wrote in message
news:945a5f59.0310160714.a6cfe8@.posting.google.com...
> Over the last two months our SQL 2000 Server has been going into
> firehose mode very frequently. Probably about 2 times a weeek. This
> is a major annoyance since we have to restart SQL every time this
> error comes up. Sometimes we can get into Enterprise Manager and view
> that a spid is blocking another spid from doing it's work. Sometimes
> we unable to view the spid and Enterprise Manager locks up. It's very
> frustrating and wonder if anybody else has came across with their
> servers?
> Thanks,
> JK
firehose mode very frequently. Probably about 2 times a weeek. This
is a major annoyance since we have to restart SQL every time this
error comes up. Sometimes we can get into Enterprise Manager and view
that a spid is blocking another spid from doing it's work. Sometimes
we unable to view the spid and Enterprise Manager locks up. It's very
frustrating and wonder if anybody else has came across with their
servers?
Thanks,
JKThere are a couple of Kbase articles on Firehose mode :-
http://support.microsoft.com/default.aspx?scid=kb;en-us;286199
and 237398
--
HTH
Ryan Waight, MCDBA, MCSE
"Josh King" <joshk@.geoaccess.com> wrote in message
news:945a5f59.0310160714.a6cfe8@.posting.google.com...
> Over the last two months our SQL 2000 Server has been going into
> firehose mode very frequently. Probably about 2 times a weeek. This
> is a major annoyance since we have to restart SQL every time this
> error comes up. Sometimes we can get into Enterprise Manager and view
> that a spid is blocking another spid from doing it's work. Sometimes
> we unable to view the spid and Enterprise Manager locks up. It's very
> frustrating and wonder if anybody else has came across with their
> servers?
> Thanks,
> JK
Firehose mode
When we try to change a row in a table we get an error
indicating that the transaction cannot be started while in
Firehose Mode.
We have tried scrolling to end of table and this has not
fixed the problem.
Any help is greatly appreciated.
Thanks in advance
RP wrote:
> When we try to change a row in a table we get an error
> indicating that the transaction cannot be started while in
> Firehose Mode.
> We have tried scrolling to end of table and this has not
> fixed the problem.
> Any help is greatly appreciated.
> Thanks in advance
I assume you are trying to change data from SQL EM? If so, have you
reviewed this article: http://support.microsoft.com/?id=286199
What about trying to change the data from Query Analyzer. Does that
work?
David G.
indicating that the transaction cannot be started while in
Firehose Mode.
We have tried scrolling to end of table and this has not
fixed the problem.
Any help is greatly appreciated.
Thanks in advance
RP wrote:
> When we try to change a row in a table we get an error
> indicating that the transaction cannot be started while in
> Firehose Mode.
> We have tried scrolling to end of table and this has not
> fixed the problem.
> Any help is greatly appreciated.
> Thanks in advance
I assume you are trying to change data from SQL EM? If so, have you
reviewed this article: http://support.microsoft.com/?id=286199
What about trying to change the data from Query Analyzer. Does that
work?
David G.
Firehose mode
When we try to change a row in a table we get an error
indicating that the transaction cannot be started while in
Firehose Mode.
We have tried scrolling to end of table and this has not
fixed the problem.
Any help is greatly appreciated.
Thanks in advanceRP wrote:
> When we try to change a row in a table we get an error
> indicating that the transaction cannot be started while in
> Firehose Mode.
> We have tried scrolling to end of table and this has not
> fixed the problem.
> Any help is greatly appreciated.
> Thanks in advance
I assume you are trying to change data from SQL EM? If so, have you
reviewed this article: http://support.microsoft.com/?id=286199
What about trying to change the data from Query Analyzer. Does that
work?
David G.|||Where exactly did you encounter this problem?
In a t-sql batch / stored procedure, etc..?
A forward-only read-only cursor is generally called a 'firehose' cursor.
This cursor is optimized for fast performance. You will be able to navigate
in a sequential FORWARD only manner. The server sends the data to the cursor
in a continous stream - and since it is read only - no inserts/updates - the
performance is very good. SQL Server decides on the kind of cursor to provide
based on various criteria - so you might not always end up with a firehose
cursor.
Reason for calling it a firehose cursor -> Keywords 'fast, forward continous
stream' - kinda resembles the water gushing out of a firemans hose.
You might have navigated to the end of the table - but the data might still
be pouring out from the server into the cursor while you were trying to
update it.
Cheers!
indicating that the transaction cannot be started while in
Firehose Mode.
We have tried scrolling to end of table and this has not
fixed the problem.
Any help is greatly appreciated.
Thanks in advanceRP wrote:
> When we try to change a row in a table we get an error
> indicating that the transaction cannot be started while in
> Firehose Mode.
> We have tried scrolling to end of table and this has not
> fixed the problem.
> Any help is greatly appreciated.
> Thanks in advance
I assume you are trying to change data from SQL EM? If so, have you
reviewed this article: http://support.microsoft.com/?id=286199
What about trying to change the data from Query Analyzer. Does that
work?
David G.|||Where exactly did you encounter this problem?
In a t-sql batch / stored procedure, etc..?
A forward-only read-only cursor is generally called a 'firehose' cursor.
This cursor is optimized for fast performance. You will be able to navigate
in a sequential FORWARD only manner. The server sends the data to the cursor
in a continous stream - and since it is read only - no inserts/updates - the
performance is very good. SQL Server decides on the kind of cursor to provide
based on various criteria - so you might not always end up with a firehose
cursor.
Reason for calling it a firehose cursor -> Keywords 'fast, forward continous
stream' - kinda resembles the water gushing out of a firemans hose.
You might have navigated to the end of the table - but the data might still
be pouring out from the server into the cursor while you were trying to
update it.
Cheers!
Firehose mode
Hi
I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"
It means that you should open a Query Analyzer window and write an UPDATE
statement.
I don't know exactly what it means. But I usualay solve this problem opening
not whole table but only e.q. TOP 10
>
It means that you should open a Query Analyzer window and write an UPDATE
statement.
>
My guess is that EM has at this point not yet retrieved all rows, and
since the result set is still open, the connection does not permit a
new operation to be initiated. Note that this has more implications
than just not being able to update the row. It also means that SQL
Server needs to keep the untrieved rows locked.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||http://support.microsoft.com/kb/286199
On Jul 23, 8:55 pm, "Oscar Santiesteban"
<o_santieste...@.bellsouth.netwrote:
>
>
>
>
>
>
- Show quoted text -|||Jason Lepack (jlepack@.gmail.com) writes:
Ah, that was a very special situation. Thanks for the link, Jason.
I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"
Can anyone explain what this is and what it means ?
Thanks
David GreenbergDavid Greenberg (davidgr@.iba.org.il) writes:
Quote:
Originally Posted by
I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"
>
Can anyone explain what this is and what it means ?
It means that you should open a Query Analyzer window and write an UPDATE
statement.
My guess is that EM has at this point not yet retrieved all rows, and
since the result set is still open, the connection does not permit a
new operation to be initiated. Note that this has more implications
than just not being able to update the row. It also means that SQL
Server needs to keep the untrieved rows locked.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Uzytkownik "David Greenberg" <davidgr@.iba.org.ilnapisal w wiadomosci
news:469C9971.7050205@.iba.org.il...
Quote:
Originally Posted by
Hi
I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"
>
Can anyone explain what this is and what it means ?
I don't know exactly what it means. But I usualay solve this problem opening
not whole table but only e.q. TOP 10
br
Bober|||Erland,
Doesn't FIREHOSE mode mean that EM has read the records in a "forward only
cursor" and has no means of updating? Hence the name "firehouse" which is a
rapid stream of data only going 1 way.
Oscar
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99707AD4B7BCCYazorman@.127.0.0.1...
Quote:
Originally Posted by
David Greenberg (davidgr@.iba.org.il) writes:
Quote:
Originally Posted by
>I'm in Enterprise Manager and have made a change to a row.
>When I try to update I get a message that says:
>Transaction cannot start, Firehose mode"
>>
>Can anyone explain what this is and what it means ?
>
It means that you should open a Query Analyzer window and write an UPDATE
statement.
>
My guess is that EM has at this point not yet retrieved all rows, and
since the result set is still open, the connection does not permit a
new operation to be initiated. Note that this has more implications
than just not being able to update the row. It also means that SQL
Server needs to keep the untrieved rows locked.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||http://support.microsoft.com/kb/286199
On Jul 23, 8:55 pm, "Oscar Santiesteban"
<o_santieste...@.bellsouth.netwrote:
Quote:
Originally Posted by
Erland,
>
Doesn't FIREHOSE mode mean that EM has read the records in a "forward only
cursor" and has no means of updating? Hence the name "firehouse" which is a
rapid stream of data only going 1 way.
>
Oscar
>
"Erland Sommarskog" <esq...@.sommarskog.sewrote in message
>
news:Xns99707AD4B7BCCYazorman@.127.0.0.1...
>
>
>
Quote:
Originally Posted by
David Greenberg (davi...@.iba.org.il) writes:
Quote:
Originally Posted by
I'm in Enterprise Manager and have made a change to a row.
When I try to update I get a message that says:
Transaction cannot start, Firehose mode"
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Can anyone explain what this is and what it means ?
>
Quote:
Originally Posted by
It means that you should open a Query Analyzer window and write an UPDATE
statement.
>
Quote:
Originally Posted by
My guess is that EM has at this point not yet retrieved all rows, and
since the result set is still open, the connection does not permit a
new operation to be initiated. Note that this has more implications
than just not being able to update the row. It also means that SQL
Server needs to keep the untrieved rows locked.
>
Quote:
Originally Posted by
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Quote:
Originally Posted by
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ons/books.mspx- Hide quoted text -
>
- Show quoted text -|||Jason Lepack (jlepack@.gmail.com) writes:
Quote:
Originally Posted by
http://support.microsoft.com/kb/286199
Ah, that was a very special situation. Thanks for the link, Jason.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Firehose mode
When we try to change a row in a table we get an error
indicating that the transaction cannot be started while in
Firehose Mode.
We have tried scrolling to end of table and this has not
fixed the problem.
Any help is greatly appreciated.
Thanks in advanceRP wrote:
> When we try to change a row in a table we get an error
> indicating that the transaction cannot be started while in
> Firehose Mode.
> We have tried scrolling to end of table and this has not
> fixed the problem.
> Any help is greatly appreciated.
> Thanks in advance
I assume you are trying to change data from SQL EM? If so, have you
reviewed this article: http://support.microsoft.com/?id=286199
What about trying to change the data from Query Analyzer. Does that
work?
David G.
indicating that the transaction cannot be started while in
Firehose Mode.
We have tried scrolling to end of table and this has not
fixed the problem.
Any help is greatly appreciated.
Thanks in advanceRP wrote:
> When we try to change a row in a table we get an error
> indicating that the transaction cannot be started while in
> Firehose Mode.
> We have tried scrolling to end of table and this has not
> fixed the problem.
> Any help is greatly appreciated.
> Thanks in advance
I assume you are trying to change data from SQL EM? If so, have you
reviewed this article: http://support.microsoft.com/?id=286199
What about trying to change the data from Query Analyzer. Does that
work?
David G.
firehose
Good day to all
How could I dis-able the read-only in a table when it generate an error message "Firehose mode"?, when I delete a record or a group of records?. What is that mean?
Thank you for any help
Madix
Hi Madix,
Could you let me know the exact error message you're getting, which product/language/API you are using and what you do that causes the error please.
Thanks,
Chris
|||Hi,guess your problem is related to:
http://support.microsoft.com/kb/286199/en-us
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q237398&
HTH, Jens Suessmeyer.
firehose
Good day to all
How could I dis-able the read-only in a table when it generate an error message "Firehose mode"?, when I delete a record or a group of records?. What is that mean?
Thank you for any help
Madix
Hi Madix,
Could you let me know the exact error message you're getting, which product/language/API you are using and what you do that causes the error please.
Thanks,
Chris
|||Hi,guess your problem is related to:
http://support.microsoft.com/kb/286199/en-us
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q237398&
HTH, Jens Suessmeyer.
订阅：
博文 (Atom)