Use DBCC OPENTRAN -- When -- Why

Check this important link from Microsoft:
=========================================
Incomplete transaction may hold large number of locks and cause blocking:
------------------------------------------------------------------------
http://support.microsoft.com/kb/295108/

Check open transactions in the database:
========================================
1): Create table 'oldtransactions'
----------------------------------:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[oldtransactions](
[db] [varchar](255) NULL,
[urenoud] [int] NULL,
[changed] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
----------------------------
2): Create Job ,on Master database "CheckOpenTran":
--------------------------------------------------
DECLARE map_cursor CURSOR FOR select name from master..sysdatabases where not name in ('master','model','msdb','tempdb','distribution')
OPEN map_cursor
FETCH NEXT FROM map_cursor INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN

create table #temp(field varchar(50),inhoud varchar(50))
insert into #temp
exec ('dbcc opentran('+@db+') with tableresults')
set @urenoud=0
select @urenoud=datediff(n, inhoud, getdate()) from #temp where field='OLDACT_STARTTIME'
drop table #temp
set @urenoud=isnull(@urenoud, 0)
insert oldtransactions(db, urenoud, changed) values (@db, @urenoud, getdate())
FETCH NEXT FROM map_cursor INTO @db
END
CLOSE map_cursor
DEALLOCATE map_cursor
------------------------
3): Schedule to run the job each 15 minutes of the day(Occurs every day every 15 minute(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 1/31/2006.)

---------------------------------------------
4): Create Stored procedure 'CheckOpenTran':
--------------------------------------------
USE [master]
GO
/****** Object: StoredProcedure [dbo].[CheckOpenTran] Script Date: 12/12/2008 13:20:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[CheckOpenTran](
@maxtime int=1
)
as
declare @db varchar(200)
declare @snapshotage int
declare @oldest int

declare @ret int
set @ret=0
declare @description varchar(200)
set @description=''

select @snapshotage=max(datediff(n, changed, getdate())) from oldtransactions

if @snapshotage<17
begin
select top 1 @db=db, @oldest=urenoud from oldtransactions where urenoud>=@maxtime order by urenoud
set @oldest=isnull(@oldest, 0)
if @oldest>0
begin
set @ret=1
set @description=@description+@db+' (transactie '+ cast(@oldest as varchar(5)) + ' minuten oud)'
End
else
begin
set @ret=0
set @description='Transacties ok'
End
end
else
begin
set @ret=1
set @description='Open Transactie SQL Job draaid niet'
end

select ret=@ret, message=@description
---------------------------
5): Execute 'CheckOpenTran' stored procedure when yoe need to check for an open transactions.