FTS Index Size: mode

显示标签为“mode”的博文。显示所有博文

2012年3月27日星期二

Flipping back to simple mode

Hi,
I have three user databases on my production server and I noticed a strange
behavior whenever the server gets rebooted. on two of the databases the
recovery model flips back from Full to simple when the server is rebooted
any time. On the third database the recover model stays as Full even after
the reboot. Even after I switch the recovery model back to full on the two
databases what I see is, as soon as the server gets rebooted, they both flip
back to simple.
I am confused as to why it is happening only with two databases and not the
third. Is there something that is set up wrongly on these two databases?.
Any insight is highly helpful.
Thanks
MeherCheck under Management | jobs in Enterprise Manager, something this is
running sp_dboption '<database','select into','true' and sp_dboption
'<database>','trunc','true'
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have three user databases on my production server and I noticed a
strange
> behavior whenever the server gets rebooted. on two of the databases the
> recovery model flips back from Full to simple when the server is rebooted
> any time. On the third database the recover model stays as Full even after
> the reboot. Even after I switch the recovery model back to full on the two
> databases what I see is, as soon as the server gets rebooted, they both
flip
> back to simple.
> I am confused as to why it is happening only with two databases and not
the
> third. Is there something that is set up wrongly on these two databases?.
> Any insight is highly helpful.
> Thanks
> Meher
>|||I checked them. No job is running with those options. I only have the
following options set to true on both the databases.
torn page detection
auto create statistics
auto update statistics
BTW I am running SQL Server 2000 with SP3 Installed.
Thanks for the suggestion Andy.
Meher
"Andy Svendsen" <andymcdba1@.NOMORESPAM.yahoo.com> wrote in message
news:ubIupIW$DHA.212@.TK2MSFTNGP12.phx.gbl...
> Check under Management | jobs in Enterprise Manager, something this is
> running sp_dboption '<database','select into','true' and sp_dboption
> '<database>','trunc','true'
> --
> ****************************************
***************************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@.NOMORESPAM.yahoo.com
> Please remove NOMORESPAM before replying.
> Always keep your antivirus and Microsoft software
> up to date with the latest definitions and product updates.
> Be suspicious of every email attachment, I will never send
> or post anything other than the text of a http:// link nor
> post the link directly to a file for downloading.
> This posting is provided "as is" with no warranties
> and confers no rights.
> ****************************************
***************************
> "Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
> news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> strange
rebooted
after
two
> flip
> the
databases?.
>

Flipping back to simple mode

Hi,
I have three user databases on my production server and I noticed a strange
behavior whenever the server gets rebooted. on two of the databases the
recovery model flips back from Full to simple when the server is rebooted
any time. On the third database the recover model stays as Full even after
the reboot. Even after I switch the recovery model back to full on the two
databases what I see is, as soon as the server gets rebooted, they both flip
back to simple.
I am confused as to why it is happening only with two databases and not the
third. Is there something that is set up wrongly on these two databases?.
Any insight is highly helpful.
Thanks
MeherCheck under Management | jobs in Enterprise Manager, something this is
running sp_dboption '<database','select into','true' and sp_dboption
'<database>','trunc','true'
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have three user databases on my production server and I noticed a
strange
> behavior whenever the server gets rebooted. on two of the databases the
> recovery model flips back from Full to simple when the server is rebooted
> any time. On the third database the recover model stays as Full even after
> the reboot. Even after I switch the recovery model back to full on the two
> databases what I see is, as soon as the server gets rebooted, they both
flip
> back to simple.
> I am confused as to why it is happening only with two databases and not
the
> third. Is there something that is set up wrongly on these two databases?.
> Any insight is highly helpful.
> Thanks
> Meher
>|||I checked them. No job is running with those options. I only have the
following options set to true on both the databases.
torn page detection
auto create statistics
auto update statistics
BTW I am running SQL Server 2000 with SP3 Installed.
Thanks for the suggestion Andy.
Meher
"Andy Svendsen" <andymcdba1@.NOMORESPAM.yahoo.com> wrote in message
news:ubIupIW$DHA.212@.TK2MSFTNGP12.phx.gbl...
> Check under Management | jobs in Enterprise Manager, something this is
> running sp_dboption '<database','select into','true' and sp_dboption
> '<database>','trunc','true'
> --
> *******************************************************************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@.NOMORESPAM.yahoo.com
> Please remove NOMORESPAM before replying.
> Always keep your antivirus and Microsoft software
> up to date with the latest definitions and product updates.
> Be suspicious of every email attachment, I will never send
> or post anything other than the text of a http:// link nor
> post the link directly to a file for downloading.
> This posting is provided "as is" with no warranties
> and confers no rights.
> *******************************************************************
> "Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
> news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have three user databases on my production server and I noticed a
> strange
> > behavior whenever the server gets rebooted. on two of the databases the
> > recovery model flips back from Full to simple when the server is
rebooted
> > any time. On the third database the recover model stays as Full even
after
> > the reboot. Even after I switch the recovery model back to full on the
two
> > databases what I see is, as soon as the server gets rebooted, they both
> flip
> > back to simple.
> >
> > I am confused as to why it is happening only with two databases and not
> the
> > third. Is there something that is set up wrongly on these two
databases?.
> > Any insight is highly helpful.
> >
> > Thanks
> >
> > Meher
> >
> >
>

2012年2月19日星期日

Fireshose mode

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.
> >.
> >

firehouse mode

SYMPTOMS
If you attempt to make changes to a row in a table
displayed in SQL Server Enterprise Manager (SEM), unless
you scroll down to the end of the table (the last row of
the table), Enterprise Manager returns the following
error:
Cannot start transaction while in firehose mode.
CAUSE
When using SEM to display the rows from a table, all rows
are returned by a "firehose cursor"; however, only the
rows that are displayed have been processed. A "firehose
cursor" refers to how the server sends rows to the client
as fast as the client can process them. Rows that are not
displayed in the Enterprise Manager are not processed and,
therefore, they remain in the network buffer.
The "Cannot start transaction while in firehose mode"
error occurs when an OLE-DB provider attempts to perform a
join transaction with results pending and while not in an
updateable cursor mode.
WORKAROUND
Scroll all the way down to the last row of the table. This
forces all the rows to be processed. You can then edit the
row needed and execute the update.

>--Original Message--
>I just got the following message after trying to update
>records in a sql table:
>"transaction cannot start while in firehouse mode"
>any ideas what the problem is? please advise and thanks
>.
>> Aaron's suggestion did not work.
What does "did not work" mean?
http://www.aspfaq.com/
(Reverse address to reply.)

firehouse mode

Firehouse mode

I am getting a message to the effect that a transaction cannot be processes
while SQL Server is in firehouse mode. whazat'Are you talking about some error message regarding a cursor in
*firehose* mode? Can you post the actual error message and the steps
you took to get there?
If you're talking about a firehose cursor message then a firehose cursor
is basically a read-only forward-only cursor. It is the default and
fastest cursor mode for retrieving data from SQL server (when using
cursors that is; but typically set-based queries are faster than
cursor-based queries).
http://msdn.microsoft.com/library/d... />
7_7d6b.asp
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
ncz wrote:

>I am getting a message to the effect that a transaction cannot be processes
>while SQL Server is in firehouse mode. whazat'
>|||Everything Mike said is accurate, so I won't add more detail about WHY it's
happening...
This is most commonly seen in Server Enterprise Manager, when you right
click a table and go "return all rows." Then, after modifying a row, you
can't save the table.
Return the row you want to modify through a query, or better yet, modify the
row from Query Analyzer.
Donna
"ncz" wrote:

> I am getting a message to the effect that a transaction cannot be processe
s
> while SQL Server is in firehouse mode. whazat'|||It basically means that the connection on which you are executing the query
has pending results - i.e. client app has not fetched the last row yet. One
possibility is that you have connection pooling enabled, but your app is not
properly cleaning up resources when queries fail, and leave connections
behind with pending results. The connection is then reused for another spid
and firehose error is raised. Try setting SET XACT_ABORT ON to force SQL to
clean up all resources on SQL error or timeout.
Seach http://support.microsoft.com for "firehose" for additional information
about this error and ways to resolve it.
Adrian
"ncz" <ncz@.discussions.microsoft.com> wrote in message
news:ECE78504-DAFD-4C2E-8762-18A3A02144BB@.microsoft.com...
>I am getting a message to the effect that a transaction cannot be processes
> while SQL Server is in firehouse mode. whazat'

firehouse mode

SYMPTOMS
If you attempt to make changes to a row in a table
displayed in SQL Server Enterprise Manager (SEM), unless
you scroll down to the end of the table (the last row of
the table), Enterprise Manager returns the following
error:
Cannot start transaction while in firehose mode.
CAUSE
When using SEM to display the rows from a table, all rows
are returned by a "firehose cursor"; however, only the
rows that are displayed have been processed. A "firehose
cursor" refers to how the server sends rows to the client
as fast as the client can process them. Rows that are not
displayed in the Enterprise Manager are not processed and,
therefore, they remain in the network buffer.
The "Cannot start transaction while in firehose mode"
error occurs when an OLE-DB provider attempts to perform a
join transaction with results pending and while not in an
updateable cursor mode.
WORKAROUND
Scroll all the way down to the last row of the table. This
forces all the rows to be processed. You can then edit the
row needed and execute the update.

>--Original Message--
>I just got the following message after trying to update
>records in a sql table:
>"transaction cannot start while in firehouse mode"
>any ideas what the problem is? please advise and thanks
>.
>
> Aaron's suggestion did not work.
What does "did not work" mean?
http://www.aspfaq.com/
(Reverse address to reply.)

Firehouse mode

I am getting a message to the effect that a transaction cannot be processes
while SQL Server is in firehouse mode. whazat?
Are you talking about some error message regarding a cursor in
*firehose* mode? Can you post the actual error message and the steps
you took to get there?
If you're talking about a firehose cursor message then a firehose cursor
is basically a read-only forward-only cursor. It is the default and
fastest cursor mode for retrieving data from SQL server (when using
cursors that is; but typically set-based queries are faster than
cursor-based queries).
http://msdn.microsoft.com/library/de...on_07_7d6b.asp
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
ncz wrote:

>I am getting a message to the effect that a transaction cannot be processes
>while SQL Server is in firehouse mode. whazat?
>
|||Everything Mike said is accurate, so I won't add more detail about WHY it's
happening...
This is most commonly seen in Server Enterprise Manager, when you right
click a table and go "return all rows." Then, after modifying a row, you
can't save the table.
Return the row you want to modify through a query, or better yet, modify the
row from Query Analyzer.
Donna
"ncz" wrote:

> I am getting a message to the effect that a transaction cannot be processes
> while SQL Server is in firehouse mode. whazat?
|||It basically means that the connection on which you are executing the query
has pending results - i.e. client app has not fetched the last row yet. One
possibility is that you have connection pooling enabled, but your app is not
properly cleaning up resources when queries fail, and leave connections
behind with pending results. The connection is then reused for another spid
and firehose error is raised. Try setting SET XACT_ABORT ON to force SQL to
clean up all resources on SQL error or timeout.
Seach http://support.microsoft.com for "firehose" for additional information
about this error and ways to resolve it.
Adrian
"ncz" <ncz@.discussions.microsoft.com> wrote in message
news:ECE78504-DAFD-4C2E-8762-18A3A02144BB@.microsoft.com...
>I am getting a message to the effect that a transaction cannot be processes
> while SQL Server is in firehouse mode. whazat?

firehouse mode

I just got the following message after trying to update
records in a sql table:
"transaction cannot start while in firehouse mode"
any ideas what the problem is? please advise and thanksWhat does your code look like? My suggestion is to not use recordset
objects in ADO to modify data. Send UPDATE statements instead...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> I just got the following message after trying to update
> records in a sql table:
> "transaction cannot start while in firehouse mode"
> any ideas what the problem is? please advise and thanks|||hi Aaron -
I am entering data directly into a table, no query.
>--Original Message--
>What does your code look like? My suggestion is to not
use recordset
>objects in ADO to modify data. Send UPDATE statements
instead...
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Lisa P" <anonymous@.discussions.microsoft.com> wrote in
message
>news:940101c478a1$06a5e660$a601280a@.phx.gbl...
>> I just got the following message after trying to update
>> records in a sql table:
>> "transaction cannot start while in firehouse mode"
>> any ideas what the problem is? please advise and thanks
>
>.
>|||Use an UPDATE statement in Query Analyzer, do not "enter data directly into
a table"...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:973d01c478a7$ed278c00$a301280a@.phx.gbl...
> hi Aaron -
> I am entering data directly into a table, no query.
> >--Original Message--
> >What does your code look like? My suggestion is to not
> use recordset
> >objects in ADO to modify data. Send UPDATE statements
> instead...
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >
> >"Lisa P" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> >> I just got the following message after trying to update
> >> records in a sql table:
> >> "transaction cannot start while in firehouse mode"
> >> any ideas what the problem is? please advise and thanks
> >
> >
> >.
> >|||SYMPTOMS
If you attempt to make changes to a row in a table
displayed in SQL Server Enterprise Manager (SEM), unless
you scroll down to the end of the table (the last row of
the table), Enterprise Manager returns the following
error:
Cannot start transaction while in firehose mode.
CAUSE
When using SEM to display the rows from a table, all rows
are returned by a "firehose cursor"; however, only the
rows that are displayed have been processed. A "firehose
cursor" refers to how the server sends rows to the client
as fast as the client can process them. Rows that are not
displayed in the Enterprise Manager are not processed and,
therefore, they remain in the network buffer.
The "Cannot start transaction while in firehose mode"
error occurs when an OLE-DB provider attempts to perform a
join transaction with results pending and while not in an
updateable cursor mode.
WORKAROUND
Scroll all the way down to the last row of the table. This
forces all the rows to be processed. You can then edit the
row needed and execute the update.
>--Original Message--
>I just got the following message after trying to update
>records in a sql table:
>"transaction cannot start while in firehouse mode"
>any ideas what the problem is? please advise and thanks
>.
>|||thanks Jim, I'll try it - Aaron's suggestion did not work.
regards!
>--Original Message--
>SYMPTOMS
>If you attempt to make changes to a row in a table
>displayed in SQL Server Enterprise Manager (SEM), unless
>you scroll down to the end of the table (the last row of
>the table), Enterprise Manager returns the following
>error:
>Cannot start transaction while in firehose mode.
>CAUSE
>When using SEM to display the rows from a table, all rows
>are returned by a "firehose cursor"; however, only the
>rows that are displayed have been processed. A "firehose
>cursor" refers to how the server sends rows to the client
>as fast as the client can process them. Rows that are not
>displayed in the Enterprise Manager are not processed
and,
>therefore, they remain in the network buffer.
>The "Cannot start transaction while in firehose mode"
>error occurs when an OLE-DB provider attempts to perform
a
>join transaction with results pending and while not in an
>updateable cursor mode.
>WORKAROUND
>Scroll all the way down to the last row of the table.
This
>forces all the rows to be processed. You can then edit
the
>row needed and execute the update.
>
>>--Original Message--
>>I just got the following message after trying to update
>>records in a sql table:
>>"transaction cannot start while in firehouse mode"
>>any ideas what the problem is? please advise and thanks
>>.
>.
>|||> Aaron's suggestion did not work.
What does "did not work" mean?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||If you are in SEM, the Jim's response is the most likely cure... Until all
of the rows which you have chosen are displayed, the cursor for displaying
those rows is still open , and the connection can not do anything else...
So scroll to the bottom, then go back up and make your changes...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Lisa P" <anonymous@.discussions.microsoft.com> wrote in message
news:940101c478a1$06a5e660$a601280a@.phx.gbl...
> I just got the following message after trying to update
> records in a sql table:
> "transaction cannot start while in firehouse mode"
> any ideas what the problem is? please advise and thanks

Firehouse mode

I am getting a message to the effect that a transaction cannot be processes
while SQL Server is in firehouse mode. whazat'This is a multi-part message in MIME format.
--050201090909050805020409
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Are you talking about some error message regarding a cursor in
*firehose* mode? Can you post the actual error message and the steps
you took to get there?
If you're talking about a firehose cursor message then a firehose cursor
is basically a read-only forward-only cursor. It is the default and
fastest cursor mode for retrieving data from SQL server (when using
cursors that is; but typically set-based queries are faster than
cursor-based queries).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
ncz wrote:
>I am getting a message to the effect that a transaction cannot be processes
>while SQL Server is in firehouse mode. whazat'
>
--050201090909050805020409
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Are you talking about some error message regarding a cursor in firehose
mode?Â Can you post the actual error message and the steps you took to
get there? 
 
If you're talking about a firehose cursor message then a firehose
cursor is basically a read-only forward-only cursor.Â It is the default
and fastest cursor mode for retrieving data from SQL server (when using
cursors that is; but typically set-based queries are faster than
cursor-based queries). 
 
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a> ">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a> 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
ncz wrote:
<blockquote cite="midECE78504-DAFD-4C2E-8762-18A3A02144BB@.microsoft.com"
type="cite">
<pre wrap="">I am getting a message to the effect that a transaction cannot be processes
while SQL Server is in firehouse mode. whazat'
</pre>
</blockquote>
</body>
</html>
--050201090909050805020409--|||Everything Mike said is accurate, so I won't add more detail about WHY it's
happening...
This is most commonly seen in Server Enterprise Manager, when you right
click a table and go "return all rows." Then, after modifying a row, you
can't save the table.
Return the row you want to modify through a query, or better yet, modify the
row from Query Analyzer.
Donna
"ncz" wrote:
> I am getting a message to the effect that a transaction cannot be processes
> while SQL Server is in firehouse mode. whazat'|||It basically means that the connection on which you are executing the query
has pending results - i.e. client app has not fetched the last row yet. One
possibility is that you have connection pooling enabled, but your app is not
properly cleaning up resources when queries fail, and leave connections
behind with pending results. The connection is then reused for another spid
and firehose error is raised. Try setting SET XACT_ABORT ON to force SQL to
clean up all resources on SQL error or timeout.
Seach http://support.microsoft.com for "firehose" for additional information
about this error and ways to resolve it.
Adrian
"ncz" <ncz@.discussions.microsoft.com> wrote in message
news:ECE78504-DAFD-4C2E-8762-18A3A02144BB@.microsoft.com...
>I am getting a message to the effect that a transaction cannot be processes
> while SQL Server is in firehouse mode. whazat'

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

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!

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"

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

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.

Firehose mode

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)

2012年3月27日星期二

Flipping back to simple mode

Flipping back to simple mode

2012年2月19日星期日

Fireshose mode

firehouse mode

firehouse mode

Firehouse mode

firehouse mode

Firehouse mode

firehouse mode

firehouse mode

Firehouse mode

Firehose mode is disrupting processes

Firehose mode

Firehose mode

Firehose mode

Firehose mode

firehose

firehose

FTS Index Size

Blog Archive

About Me