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 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: References: <2e7b480a-ee84-4105-a3e4-b9297e4d9835@googlegroups.com> <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 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