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.
Thanks,
Paul
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.sql
