Groups > comp.databases.ms-sqlserver > #1466

Re: How to find not-committed transaction in SQL Server 2008 exactly?

Path	csiph.com!usenet.pasdenom.info!news.albasani.net!eternal-september.org!feeder.eternal-september.org!mx05.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: How to find not-committed transaction in SQL Server 2008 exactly?
Date	Mon, 6 May 2013 07:16:36 +0000 (UTC)
Organization	Erland Sommarskog
Lines	33
Message-ID	<XnsA1B85EF7920DFYazorman@127.0.0.1> (permalink)
References	<2e7b480a-ee84-4105-a3e4-b9297e4d9835@googlegroups.com> <e3c72b49-9dce-44d7-885a-8e3db745110e@googlegroups.com> <XnsA1B07450DFBD3Yazorman@127.0.0.1> <5ebd7255-4062-4229-88ef-20f55934e447@googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=us-ascii
Content-Transfer-Encoding	7bit
Injection-Date	Mon, 6 May 2013 07:16:36 +0000 (UTC)
Injection-Info	mx05.eternal-september.org; posting-host="5d404a5d864426cbf50ddbcd9433a6e4"; logging-data="18526"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/lq113IkjAGL9qp8si5pA7"
User-Agent	Xnews/2005.10.03 Mime-proxy/1.4.c.4 (Win32)
Cancel-Lock	sha1:RGTMKeo80po83utJQBd8N0QsEkU=
Xref	csiph.com comp.databases.ms-sqlserver:1466

Show key headers only | View raw

Peng Liu (liupengwyy@gmail.com) writes:
> According to your reply, do you mean that there might exist open
> transactions even the result of DBCC OPENTRAN indicates no active open
> transaction? 

Yes, what DBCC OPENTRAN tells is that there is no uncommitted transaction 
in the transaction log.

Or, in another words, DBCC OPENTRAN tells you that there no uncommitted 
updates. But there might still be open transactions that hitherto only has 
performed read operations (but which might hold locks, depending on the
isolation level.)

> From the step I list, can you find that there is still open transaction?
> from which step? 

1 and 2.

> From the result of "select spid, lastwaittype, last_batch, status,
> open_tran, cmd, sql_handle from sys.sysprocesses where spid = 53;", the
> status value is sleeping, what does the "sleeping" mean? 

The process is not running. It could be because it is awating a command 
from the client, but it could also be waiting on I/O completion or a
locked resource to become available.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-04-27 00:11 -0700
  Re: How to find not-committed transaction in SQL Server 2008 exactly? Erland Sommarskog <esquel@sommarskog.se> - 2013-04-27 11:42 +0200
  Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-04-27 19:57 -0700
    Re: How to find not-committed transaction in SQL Server 2008 exactly? Erland Sommarskog <esquel@sommarskog.se> - 2013-04-28 11:26 +0200
      Re: How to find not-committed transaction in SQL Server 2008 exactly? rja.carnegie@gmail.com - 2013-04-28 03:49 -0700
      Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-05-05 20:35 -0700
        Re: How to find not-committed transaction in SQL Server 2008 exactly? Erland Sommarskog <esquel@sommarskog.se> - 2013-05-06 07:16 +0000
          Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-05-06 18:40 -0700
            Re: How to find not-committed transaction in SQL Server 2008 exactly? Erland Sommarskog <esquel@sommarskog.se> - 2013-05-07 21:12 +0200
  Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-04-28 00:52 -0700
  Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-05-05 20:31 -0700
  Re: How to find not-committed transaction in SQL Server 2008 exactly? work4anvesh@gmail.com - 2015-08-26 10:47 -0700
  Re: How to find not-committed transaction in SQL Server 2008 exactly? work4anvesh@gmail.com - 2015-08-26 10:48 -0700
  Re: How to find not-committed transaction in SQL Server 2008 exactly? johnsonwelch2@gmail.com - 2016-04-14 01:46 -0700

csiph-web