spid info

[kavira]

Hi,
Here i have a question..
Whenever i see my spid in LOCK/PROCESS ID
one command DBCC BUFFERINPUT(MY SPID) always running
and it is showing ''''tempdb.dbo.##lockinfo'&#39 ;'
MODE---- X
Is it pretty normal or something wrong..
i am not running any thing, just simply opened EM..
Pls any suggestions...
thank u

kavira

[Ray Miao]

It's normal.


------------
kavira at 5/12/00 11:49:36 AM

Hi,
Here i have a question..
Whenever i see my spid in LOCK/PROCESS ID
one command DBCC BUFFERINPUT(MY SPID) always running
and it is showing ''''tempdb.dbo.##lockinfo'&#39 ;'
MODE---- X
Is it pretty normal or something wrong..
i am not running any thing, just simply opened EM..
Pls any suggestions...
thank u

kavira

[AB]

Hi Kavira,

Well, it is not so difficult. Looking around a little bit you arrive quickly to the following conclusions.

I first discovered that the &#39;##lockinfo<n>&#39; table is created by &#39;sp_MSset_current_activity&#39;. This was easy - try this:

USE master
SELECT * FROM syscomments
WHERE text LIKE &#39;%##lockinfo%&#39;

Here comes the explanation. What is Enterprise Manager? It is a database application which allows you to work with the &#34;system&#34; data from a database through two interfaces:

1) the SQL DMO interface, which is an object based interface specific to SQL server, giving you access to more information and facilities then the first interfaces (e.g. for scripting an object).

2) an ordinary data access interface (like OLE DB or OBBC), to access the system tables directly through SQL statements and indirectly, through system or custom stored procedures: note that is done by EM through SQL DMO too, because SQL DMO allows you also to execute SQL queries and to process their result.

What is the relation between this and your &#34;##lockinfo<n>&#34; table? As any well written database application, EM encapsulates its database operations within stored procedures executed through its second interface).

When you display &#34;Current activity&#34; in EM, EM executes the undocumented procedure &#34;sp_MSset_current_activity&#34;. This procedure stores the information about the current processes and locks into two global temporary tables, called ##lockinfo<n> and ##procinfo<n>, where <n> is the SPID of the EM process.

If you are curious, please execute in QA:

DECLARE @id INT

DECLARE @lockinfo varchar(99)
DECLARE @procinfo varchar(99)

EXEC sp_MSset_current_activity @id OUTPUT

SET @procinfo = &#39;##procinfo&#39;+ convert(varchar(5), @id)
SET @lockinfo = &#39;##lockinfo&#39;+ convert(varchar(5), @id)

EXEC(&#39;select * from &#39; + @procinfo)
EXEC(&#39;select * from &#39; + @lockinfo)

You can see in the output of the second query the ##lockinfo<n> table that you mentioned, because it was used internally by &#39; sp_MSset_current_activity&#39; at the moment of time when it was collecting information about all the current processes and locks from the server.

Note that this is exactly the information displayed by EM as &#34;Current Activity&#34;.

Please have a look at the code of the stored procedure &#34;sp_MSset_current_activity&#34;

What about these strange table names, ##lockinfo<n> and ##procinfo<n>, which are global temporary table, with process-specific names.. Its simple:

- These tables have to be temporary.

- The tables are created in the &#39;sp_MSset_current_activity&#39; stored procedures and read after the execution of the procedure by the calling program/script, and therefore they must be global (a local temporary table, like #lockinfo, would be dropped automatically at the termination of the stored procedure). Actually these tables play the role of output parameters for &#39;sp_MSset_current_activity&#39;.

- These table have to be EM-instance specific: this is implemented by including the SPID number of the EM instance in their table name. In this way more than one EM instance can be active at the same time, and each of them can display its own &#34;Current Activity&#34; (displayed at different moments of time, the information is more or less different from one EM instance to another).

Regards, AB

------------
kavira at 5/12/00 11:49:36 AM

Hi,
Here i have a question..
Whenever i see my spid in LOCK/PROCESS ID
one command DBCC BUFFERINPUT(MY SPID) always running
and it is showing &#39;&#39;&#39;&#39;tempdb.dbo.##lockinfo&#39;&#39 ;&#39;
MODE---- X
Is it pretty normal or something wrong..
i am not running any thing, just simply opened EM..
Pls any suggestions...
thank u

kavira