|
Home > Archive > MS SQL Server Tools > April 2005 > InputBuffer for System Processes
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
InputBuffer for System Processes
|
|
|
| Is there any way to see what statement a system process is executing?
Currently I can use dbcc inputbuffer(spid) to show a user process, but when I
try to use a system spid I get and empty string returned.
Is there another command that can be used to look at system processes?
| |
| Paul S Randal [MS] 2005-04-18, 1:23 pm |
| System processes don't execute t-sql statements - they run internal code in
SQL Server. DBCC INPUTBUFFER will only return t-sql for user connections.
What are you trying to monitor/discover?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thom" <Thom@discussions.microsoft.com> wrote in message
news:9119B998-3DAA-4645-83C5- 53FD103617D0@microso
ft.com...
> Is there any way to see what statement a system process is executing?
> Currently I can use dbcc inputbuffer(spid) to show a user process, but
when I
> try to use a system spid I get and empty string returned.
>
> Is there another command that can be used to look at system processes?
>
>
| |
|
| I am trying to track down a system process that is at the head of blocking
chain. It happen intermitently during the day, and quite often the head is a
task manager process. I would like to be able to show the client what is
causing it.
"Thom" wrote:
> Is there any way to see what statement a system process is executing?
> Currently I can use dbcc inputbuffer(spid) to show a user process, but when I
> try to use a system spid I get and empty string returned.
>
> Is there another command that can be used to look at system processes?
>
>
| |
| Paul S Randal [MS] 2005-04-19, 11:23 am |
| What's the SPID of the thread at the head of the chain?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thom" <Thom@discussions.microsoft.com> wrote in message
news:03BB07D8-EF21-47B4-82C0- 1140C8843BF9@microso
ft.com...
> I am trying to track down a system process that is at the head of blocking
> chain. It happen intermitently during the day, and quite often the head
is a[color=darkred]
> task manager process. I would like to be able to show the client what is
> causing it.
>
>
> "Thom" wrote:
>
when I[color=darkred]
| |
|
| It has been 21/24 and 25 this week so far. I have added the sp_who2 output,
perhaps this will help clarify the issue:
25 is at the head the chain below, 91 is also a head but it is quick to
release.
-------------------------------------------------------
1 BACKGROUND sa . . LAZY
WRITER 9609 0 04/07 23:25:22
1
2 sleeping sa . . LOG
WRITER 183953 0 04/07 23:25:22
2
3 BACKGROUND sa . . master
SIGNAL HANDLER 31 0 04/07 23:25:22
3
4 BACKGROUND sa . . LOCK
MONITOR 1734 0 04/07 23:25:22
4
5 BACKGROUND sa . . MAP
GHOST CLEANUP 12935797 28358 04/07 23:25:22
5
6 BACKGROUND sa . . master TASK
MANAGER 26484 388 04/07 23:25:22
6
7 sleeping sa . .
CHECKPOINT SLEEP 164328 2633 04/07 23:25:22
7
9 BACKGROUND sa . . master TASK
MANAGER 0 0 04/07 23:25:22
9
10 BACKGROUND sa . . master TASK
MANAGER 0 0 04/07 23:25:22
10
12 BACKGROUND sa . . master TASK
MANAGER 0 0 04/07 23:25:22
12
13 BACKGROUND sa . . master TASK
MANAGER 0 0 04/07 23:25:22
13
14 BACKGROUND sa . . master TASK
MANAGER 0 0 04/07 23:25:22
14
15 BACKGROUND sa . . master TASK
MANAGER 0 0 04/07 23:25:22
15
17 BACKGROUND sa . . master TASK
MANAGER 0 23724 04/07 23:25:22
17
19 BACKGROUND sa . . master TASK
MANAGER 0 52152 04/07 23:25:22
19
20 BACKGROUND sa . . master TASK
MANAGER 0 28273 04/07 23:25:22
20
21 BACKGROUND sa . . master TASK
MANAGER 0 28886 04/07 23:25:22
21
22 BACKGROUND sa . . master TASK
MANAGER 9368813 29678 04/07 23:25:22
22
24 BACKGROUND sa . . master TASK
MANAGER 2802344 28630 04/07 23:25:22
24
25 BACKGROUND sa . . master TASK
MANAGER 3709922 29517 04/07 23:25:22
25
29 BACKGROUND sa . . master TASK
MANAGER 0 0 04/07 23:25:22
29
68 sleeping fastar FDYRS931 89
MAP SELECT 2485 119 04/18 13:16:47
Microsoft(R) Windows (R) 2000 Operating System 68
74 sleeping MGROUPNET\F4K-Extra FDYRS663
. MAP AWAITING COMMAND 439 91 04/13
11:43:00 MS SQLEM 74
76 sleeping fastar FDYRS931 91
MAP INSERT 266 17 04/18 13:16:46
Microsoft(R) Windows (R) 2000 Operating System 76
78 sleeping fastar FDYRS931 89
MAP SELECT 9750 294 04/18 13:16:47
Microsoft(R) Windows (R) 2000 Operating System 78
89 sleeping fastar FDYRS931 25
MAP SELECT 63 0 04/18 13:16:46
Microsoft(R) Windows (R) 2000 Operating System 89
90 sleeping fastar FDYRS931 89
MAP SELECT 47 0 04/18 13:16:47
Microsoft(R) Windows (R) 2000 Operating System 90
91 sleeping fastar FDYRS931 .
MAP INSERT 31 4 04/18 13:16:46
Microsoft(R) Windows (R) 2000 Operating System 91
92 sleeping fastar FDYRS931 89
MAP SELECT 16 0 04/18 13:16:47
Microsoft(R) Windows (R) 2000 Operating System 92
93 sleeping fastar FDYRS931 89
MAP SELECT 16 0 04/18 13:16:47
Microsoft(R) Windows (R) 2000 Operating System 93
97 sleeping fastar FDYRS931 89
MAP SELECT 0 0 04/18 13:16:47
Microsoft(R) Windows (R) 2000 Operating System 97
98 sleeping fastar FDYRS931 89
MAP SELECT 0 0 04/18 13:16:47
Microsoft(R) Windows (R) 2000 Operating System 98
99 sleeping fastar FDYRS931 89
MAP SELECT 0 0 04/18 13:16:47
Microsoft(R) Windows (R) 2000 Operating System 99
100 sleeping fastar FDYRS931 89
MAP SELECT 0 0 04/18 13:16:47
Microsoft(R) Windows (R) 2000 Operating System 100
101 sleeping fastar FDYRS931 89
MAP SELECT 0 0 04/18 13:16:48
Microsoft(R) Windows (R) 2000 Operating System 101
104 sleeping fastar FDYRS931 89
MAP SELECT 0 0 04/18 13:16:48
Microsoft(R) Windows (R) 2000 Operating System 104
106 sleeping fastar FDYRS931 89
MAP SELECT 0 0 04/18 13:16:49
Microsoft(R) Windows (R) 2000 Operating System 106
107 sleeping fastar FDYRS931 89
MAP SELECT 16 0 04/18 13:16:49
Microsoft(R) Windows (R) 2000 Operating System 107
"Thom" wrote:
[color=darkred]
> I am trying to track down a system process that is at the head of blocking
> chain. It happen intermitently during the day, and quite often the head is a
> task manager process. I would like to be able to show the client what is
> causing it.
>
>
> "Thom" wrote:
>
| |
| Kristy 2005-04-21, 8:24 pm |
| I use the below SPs (ran on the master database.) If you want more detailed
info on them, type the SP name in google and that will give you info. I've
modified them then there original form to suit me, but not really by that
much.
--Kristy
---------------------------------------------
CREATE PROC dbo.ShowCodeLine
(
@SPID smallint,
@WAIT tinyint = 0,
@NoLoop bit = 0
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql_handle binary(20), @handle_found bit
DECLARE @stmt_start int, @stmt_end int
DECLARE @line nvarchar(4000), @wait_str varchar(8),
@dbname varchar(30)
SET @handle_found = 0
IF @WAIT NOT BETWEEN 0 AND 60
BEGIN
RAISERROR('Valid values for @WAIT are from 0 to 60 seconds', 16, 1)
RETURN -1
END
ELSE
BEGIN
SET @wait_str = '00:00:' + RIGHT('00' + CAST(@WAIT AS varchar(2)), 2)
END
WHILE 1 = 1
BEGIN
SELECT @sql_handle = sql_handle,
@dbname = DB_NAME(dbid),
@stmt_start = stmt_start/2,
@stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID
AND ecid = 0
IF @sql_handle = 0x0
BEGIN
IF @handle_found = 0
BEGIN
RAISERROR('Cannot find handle or the SPID is invalid', 16, 1)
RETURN -1
END
ELSE
BEGIN
RAISERROR('Query/Stored procedure completed', 0, 1)
RETURN 0
END
END
ELSE
BEGIN
SET @handle_found = 1
END
SET @line =
(
SELECT
SUBSTRING( text,
COALESCE(NULLIF(@stm
t_start, 0), 1),
CASE @stmt_end
WHEN -1
THEN DATALENGTH(text)
ELSE
(@stmt_end - @stmt_start)
END
)
FROM ::fn_get_sql(@sql_ha
ndle)
)
--RAISERROR(@line, 0, 1) WITH NOWAIT
select @spid as spid,
@line as command
, @dbname as [Database Name]
IF @NoLoop = 1
BEGIN
RETURN 0
END
WAITFOR DELAY @wait_str
END
END
GO
------------------------------------------------
CREATE PROCEDURE USP_LOCKTREE
AS
SET NOCOUNT ON
BEGIN
SELECT * from master.dbo.sysprocesses (nolock) where blocked <> 0 AND DBID =
7 /*REPLACE DBID WITH THE DATABASE ID YOU WANT TO LOOK AT*/
SELECT P.SPID, P.BLOCKED, RTRIM(P.HOSTNAME) HOSTNAME
INTO MASTER..LOCKS
FROM MASTER..SYSPROCESSES P
WHERE P.BLOCKED <> 0
OR SPID IN (SELECT BLOCKED FROM MASTER..SYSPROCESSES)
AND BLOCKED=0
ORDER BY P.BLOCKED, P.SPID
DECLARE @COUNT INT
SET @COUNT = (SELECT SPID FROM MASTER..LOCKS WHERE BLOCKED = 0)
SELECT ' ' + REPLICATE (' ', LVL) + RTRIM(HOSTNAME)+ ' ('+ CAST(SPID AS
VARCHAR)+')' AS "BLOCKING LOCKS"
FROM UFN_GETLOCKSTREE(@CO
UNT)
ORDER BY PATH
declare @myspid smallint
declare @newcount int
set @newcount = (SELECT count(SPID) FROM MASTER..LOCKS)
while @newcount > 0 begin
set @myspid = (select top 1 SPID from MASTER..LOCKS)
exec dbo.ShowCodeLine @myspid, 0 ,1
delete from MASTER..LOCKS where SPID = @myspid
set @newcount = (SELECT SPID FROM MASTER..LOCKS)
end
DROP TABLE MASTER..LOCKS
END
SET NOCOUNT OFF
GO
"Thom" <Thom@discussions.microsoft.com> wrote in message
news:9119B998-3DAA-4645-83C5- 53FD103617D0@microso
ft.com...
> Is there any way to see what statement a system process is executing?
> Currently I can use dbcc inputbuffer(spid) to show a user process, but
when I
> try to use a system spid I get and empty string returned.
>
> Is there another command that can be used to look at system processes?
>
>
|
|
|
|
|