From: liupengwyy@gmail.com   
      
   According to your reply, do you mean that there might exist open transactions   
   even the result of DBCC OPENTRAN indicates no active open transaction?   
      
   From the step I list, can you find that there is still open transaction? from   
   which step?   
      
   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?   
      
   On Sunday, April 28, 2013 5:26:03 PM UTC+8, Erland Sommarskog wrote:   
   > Peng Liu (liupengwyy@gmail.com) writes:   
   >   
   > > 6. Finally, I run "DBCC OPENTRAN;", and get below: No active open   
   >   
   > > transactions. DBCC execution completed. If DBCC printed error messages,   
   >   
   > > contact your system administrator.   
   >   
   > >   
   >   
   > > So, according to what I test above, before step 6, it seems that there   
   >   
   > > is one transaction which is not committed, the session 53 has one   
   >   
   > > transaction (the "open_tran" value is 1), the status is "sleeping";   
   >   
   > > However, step 6 also show that no transaction is not committed. They   
   >   
   > > seems conflict.   
   >   
   >   
   >   
   > I don't think so. Books Online says in the Remarks section for DBCC   
   >   
   > OPENTRAN:   
   >   
   >   
   >   
   >    Use DBCC OPENTRAN to determine whether an open transaction exists within   
   >   
   >    the transaction log.   
   >   
   >   
   >   
   > You have a transaction that this far has only read data, but has not   
   >   
   > performed any updates. Therefore it is not preventing the log from being   
   >   
   > truncated.   
   >   
   >   
   >   
   > > Besides, for the query text from step 5, is it possible for me to know   
   >   
   > > the exact value of the parameters P1, P2 and P3?   
   >   
   >   
   >   
   > Only if you have a trace running which captures the statement.   
   >   
   >   
   >   
   >   
   >   
   >   
   >   
   > --   
   >   
   > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
   >   
   >   
   >   
   > Links for SQL Server Books Online:   
   >   
   > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   >   
   > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)