August 5, 2010 at 3:24 am
Hello all,
Can anyone enlighten me with this? If I run the following command
DECLARE @CMD NVARCHAR(4000)
SET @CMD = '
dbcc opentran([?]) WITH TABLERESULTS, NO_INFOMSGS'
exec sp_msforeachdb @CMD
I get results sometimes with a non numeric SPID and an OLDACT_NAME of
NoLongerClean
An example of the full output can be seen below;
OLDACT_SPID21s
OLDACT_UID-1
OLDACT_NAMENoLongerClean
OLDACT_LSN(320:456:1)
OLDACT_STARTTIMEAug 5 2010 10:21:22:270AM
OLDACT_SID0x0
It's not too much of a problem as I can filter these out, I was just interested if anyone could explain what this is and why it occurs?
Can anyone replicate these results running the statement on their SQL instance?
I'm running this on the following version;
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Thanks for any pointers
Phil
August 5, 2010 at 4:27 am
Phil Harbour (8/5/2010)
OLDACT_SPID21s
The s denotes that it's a system SPID, rather than a user process.
Because on SQL 2005 the system SPIDs can have session_ids above 50, the old rule (on SQL 2000) of <=50 = system, > 50 = user is no longer sufficient. Hence you'll see SPIDs with an s after the number indicating that it's a system process.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2010 at 5:04 am
Even I could find that on some of the production servers. As Gail rightly mentioned they are system spids.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 5, 2010 at 7:22 am
Thanks for the explaination. Should have given it a little more thought myself! Much appreciated though, another little bit of knowledge learnt thanks
November 8, 2010 at 4:42 am
Hi All,
I see this result result when I run DBCC OPENTRAN.
Please help me to understand this.
Transaction information for database 'Col_Data'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (89755:86:19)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
November 8, 2010 at 5:14 am
This is the SPID that indicates there a few transactions to be replicated on that database
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 8, 2010 at 5:31 am
Adiga (11/8/2010)
This is the SPID that indicates there a few transactions to be replicated on that database
Not quite. (no spid in sight)
The LSNs indicate that transactional replication is set up and the log reader has never ever run (the 0 LSN)
Please post new questions in a new thread in future.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply