Friday, March 23, 2012

Open_tran in sys.sysprocesses has unexpected count

I'm running SQL Server 2005 SP1 on WinXP SP2.

I'm running a query to look for open transactions or blocked transactions. The actual query I'm running registers as having 2 open transactions and I can't figure out why. It seems to have something to do with the temp table because I don't get the open transactions when there is no temp table. I could perhaps see why I might have 1 open transaction pertaining to the open temp table, but why 2. Here is the query and the data from sys.sysprocesses:

IF object_id('tempdb.dbo.#MyProcesses','U') is not NULL
BEGIN DROP table #MyProcesses; END
DECLARE @.MyVariable varchar(100)
, @.Mycmd nvarchar(1000)
, @.LoginTime int
, @.LastBatch int
, @.LastBatchFilter datetime
, @.LoginTimeFilter datetime;
SELECT @.MyVariable = '' , @.LoginTime = -600 , @.LastBatch = -145 ;

SELECT @.LastBatchFilter = DATEADD(mi,@.LastBatch,Current_timestamp);
SELECT @.LoginTimeFilter = DATEADD(mi,@.LoginTime,Current_timestamp);

-- I've reduced the number of columns in my SELECT for this example
SELECT b.[name] MyDB
, a.spid
, a.blocked
, a.open_tran
, RTRIM(a.program_name) program_name
INTO #MyProcesses
FROM sys.sysprocesses a
JOIN sys.sysdatabases b on a.dbid = b.dbid
WHERE (a.blocked = 1 or a.open_tran > 0);

Select * from #MyProcesses

MyDB spid blocked open_tran program_name
master 56 0 2 Microsoft SQL Server Management Studio - Query

Please educate me.



The "transaction count" is not really that important. There are many things which implicitly create transactions. The important item is "blocked".

In your case, creating a temp table with an @.tablename creates a transaction around the query, so when it ends, it "rolls back" the temp table and deletes it. This is an internal mechanisim and not under your control.

No comments:

Post a Comment