Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

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

Show all headers | View raw

On Sunday, 28 April 2013 10:26:03 UTC+1, Erland Sommarskog  wrote:
> 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.

That's interesting and subtle.  But I think it contradicts 
the headline unless we give to the term "active transaction" 
the meaning "performing updates" - which probably should be explained
somewhere.  So, that's the kind of transaction that this detects?
<http://msdn.microsoft.com/en-us/library/ms182792%28v=sql.105%29.aspx>

Does it also support a point of view that "database console commands"
are appropriate for system maintenance, but not for programming,
except for troubleshooting?  (This is troubleshooting, I suppose.)

In this example, what the command tells you seems to be determined
by the designer's assumption about why you want to know - it's 
written for the maintenance scenario.

Another type of evidence about transactions is locks.  Maybe the
available information about locks will tell you what you want to
know.  But there are different types of lock - row, page, table -
and the relationship between a Transact-SQL statement and the 
locks that are put on in executing it isn't simple, unless you
force them with "hints".  I don't know if that rules out an 
approach to this sort of question using locks.

With or without thinking about locks, you could put in statements
whose purpose is only to create evidence about the state of the 
transaction, such as an UPDATE on a table just to take up an entry
in the transaction log.  (I think that an UPDATE that affects no
rows won't do, but an UPDATE that sets a column to its existing 
value will.)  But this may be wasteful of the system's resources
and probably should be taken out of the program when you don't 
need it for diagnostic information.

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