Uncommitted Transactions

One way to find out if a transaction is causing a block is with the sp_who2 system stored procedure.  The scope of this procedure is server-wide.  It identifies all connections to the database, the login name, the application that is running, and in the context of locks and blocks, any SPIDs that are causing a block.  The offending SPID will be in the BlkBy column.
I often find myself using transactions when doing DELETE or UPDATE operations to allow myself the option of ROLLING back the transaction in case something goes awry.  As equally often, I forget to COMMIT the transaction and move on the the next order of business, resulting in a lock on the table and essentially blocking everyone out of it’s use (unless they use the with (nolock) hint in their query).
If I ran the following query and forgot to commit the transaction what would happen?

USE AdventureWorks;
GO

BEGIN TRAN
INSERT INTO person.contact (FirstName, LastName, EmailAddress, PasswordHash, PasswordSalt)
VALUES ('Dave', 'Turpin', 'dave.turpin@acm.org', 'goblygook', 'Salt');

The table would lock up until either a commit or rollback happens.  But how would I know if there are any open transactions, either mine or somebody else?  sp_who2 would not indicate any blocks because there aren’t any.  There’s only an open transaction.  But as long as no other processes are tying to use that table there will not be any blocking/locking issues.
DBCC OPENTRAN will list the earliest open transaction.  In our simple example, the DBCC will return:

In this case we can see that SPID 54 had a transaction open since 5:08AM.  Now we can take a look at SPID 54, see who it is, and initiate either a rollback or commit.  Chances are if an uncommitted transaction is hanging out there there’s either a major bug in someone’s code, we have a legitimate locking/blocking problem, or its an ad hoc batch.

Since DBCC OPENTRAN only identifies the earliest open transaction, you may have to resolve someone else’s transaction before actually getting to yours.  Hopefully there will not be any open transactions for very long and the point is moot.