question on Transactions and Commit

If you start a transaction with BEGIN TRAN and then, for some reason this is
interrupted and not recovered from (in other words, no ROLLBACK or no
COMMIT), what happens? Is there a time after which this will timeout? If
so, where is it set and what is the deafault?
I have a situation where i believe this happened, and now queries to the
table simply hang, do not return and do not give any error message. More
specifically if the query contains a WHERE clause. For instance, the query
SELECT * from Table returns quickly (it is a small table). But SELECT *
from Table WHERE column='xxx' is the one that hangs.
Any advice is appreciated. Thank you.I would expect it to actually be the other way around. But in any case an
open tran will stay that way for as long as the connection that started it
is valid. Your application that begins the tran should always handle
situations sucha s that. You can check to see if there is an open tran in
any db by running DBCC OPENTRAN(). If you find one that is hanging around
long after it should be you can kill that SPID and the tran will be rolled
back automatically. You can also use sp_who2 to see if you are begin blocked
and by who. sp_lock will also show any currently held locks.
Andrew J. Kelly SQL MVP
"THOMAS CONLON" <someone@.verizon.net> wrote in message
news:bJlgg.2906$td6.2754@.trnddc02...
> If you start a transaction with BEGIN TRAN and then, for some reason this
> is interrupted and not recovered from (in other words, no ROLLBACK or no
> COMMIT), what happens? Is there a time after which this will timeout? If
> so, where is it set and what is the deafault?
> I have a situation where i believe this happened, and now queries to the
> table simply hang, do not return and do not give any error message. More
> specifically if the query contains a WHERE clause. For instance, the
> query SELECT * from Table returns quickly (it is a small table). But
> SELECT * from Table WHERE column='xxx' is the one that hangs.
> Any advice is appreciated. Thank you.
>|||THOMAS CONLON (someone@.verizon.net) writes:
> If you start a transaction with BEGIN TRAN and then, for some reason
> this is interrupted and not recovered from (in other words, no ROLLBACK
> or no COMMIT), what happens? Is there a time after which this will
> timeout? If so, where is it set and what is the deafault?
There is no timeout. If there is never any COMMIT or ROLLBACK explicitly,
there will be a ROLLBACK when the connection is physically disconnected.
Physical disconnection happens when the application exits. It can also
happen if the application code issues a disconnect. However, usually
connection is in force in the client API. This causes the physical
connection to be retained for 60 seconds, unless it can be reused in this
time frame. If a connection is reused from the pool, the rollback will
occur on reuse.

> I have a situation where i believe this happened, and now queries to the
> table simply hang, do not return and do not give any error message.
> More specifically if the query contains a WHERE clause. For instance,
> the query SELECT * from Table returns quickly (it is a small table).
> But SELECT * from Table WHERE column='xxx' is the one that hangs.
Note that if you get a "Timeout expired" in your application, you must
submit a IF @.@.trancount > 0 ROLLBACK TRANSACTION". The timeout is
something the client API raises, and SQL Server does not know what it
means, and will keep the transaction alive.
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

Question on Process killed and still exists with a killed/rollback

Hi
I have killed a process running from sql server agent. the process id is 55
and when i give sp_who2 it still shows the process as killed /rollback. whe
n
i give the command kill 55 it says 100% rollback complete, but still the
process exists. Any suggestions to kill this process completely.
Thanks
RodgerRodger,
You cannot kill a process that is in the middle of Rolling back (it
must be a rather long transaction). Unfortunately you will need to
wait patiently until the rollback finishes. Use Kill WITH STATUS ONLY
will show you how fast the rollback has finished.
Attempt to stop a rollback process e.g. restart the SQL Instance will
only cause the rollback run even longer.|||Someone asked essentially the same question just a few days ago. Your
process may be blocked or perhaps it had a massive amount of uncommitted
data changes when you first attempted to kill it, and this thread discusses
various options.
http://groups.google.com/group/micr...1637c15f84fa05f
Another consideration going forward is to verify that your database
transaction log is located on a seperate physical disk from your database
data file. When rolling back a large transaction, both the data and log
files get hit heavily and keeping them on seperate drives decreases the time
required to recover due to parallel I/O processing.
"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:3F59A2B2-9606-4409-8434-39DF9D4366C0@.microsoft.com...
> Hi
> I have killed a process running from sql server agent. the process id is
> 55
> and when i give sp_who2 it still shows the process as killed /rollback.
> when
> i give the command kill 55 it says 100% rollback complete, but still the
> process exists. Any suggestions to kill this process completely.
> Thanks
> Rodger

Question on Process killed and still exists with a killed/roll

Sometimes it does take a little bit time to finish off the rollback
(despite it indicates it is 100% completed'), but if the process still
there after let say good 10 mins. Run 'select * from sysprocesses' on
the master database and see if there is any lock problem there. Check
the 'blocked' field of the process.
Also check the SQL logs to see for any exceptions/errors. What SQL
platform you are on? What Service Pack you have installed.
Regards,
Mel
"MSLam" wrote:

> Sometimes it does take a little bit time to finish off the rollback
> (despite it indicates it is 100% completed'), but if the process still
> there after let say good 10 mins. Run 'select * from sysprocesses' on
> the master database and see if there is any lock problem there. Check
> the 'blocked' field of the process.
> Also check the SQL logs to see for any exceptions/errors. What SQL
> platform you are on? What Service Pack you have installed.
> Regards,
> Mel
>