Track down runaway CPU spikes

Question

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Animal Magic SSChampion Points: 13964 More actions · Answer 1

Peter - silly question i know but did you actually put the KPID id in the script where it says to?

Peter D-251663 Ten Centuries Points: 1010 More actions · Answer 2

This is what I have:

USE master

GO

-- returns SPID & DBCC INPUTBUFFER for a given kpid

DECLARE @sql nvarchar(1000), @spid int, @ParamDef nvarchar(1000)

SET @ParamDef = N'@spidOUT int OUTPUT'

SET @sql = N'SELECT @spidOUT = spid '

+ 'FROM master..sysprocesses '

+ 'WHERE kpid = ' 7116

EXEC sp_executesql @sql, @ParamDef, @spidOUT = @spid OUTPUT

SET @sql = N'SELECT @spidOUT AS [SPID];dbcc inputbuffer('

+ CAST(@spid AS nvarchar(3)) + N')'

EXEC sp_executesql @sql, @ParamDef, @spid

Server: Msg 170, Level 15, State 1, Line 8

Line 8: Incorrect syntax near '7116'.

Tried a few variations of (') but no luck

Richard M. SSCertifiable Points: 7401 More actions · Answer 3

Peter D (7/22/2009)
+ 'WHERE kpid = ' 7116

kpid is numeric, so you should change that to:

+ 'WHERE kpid = 7116 '

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Animal Magic SSChampion Points: 13964 More actions · Answer 4

Richard M (7/22/2009)
Peter D (7/22/2009)
+ 'WHERE kpid = ' 7116
kpid is numeric, so you should change that to:
+ 'WHERE kpid = 7116 '

its not because its numeric, its because its outside of the dynamic string build. your code fixes it though 🙂

Richard M. SSCertifiable Points: 7401 More actions · Answer 5

... I know.. I just wanted it to be as simple as possible for the OP 😉

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Peter D-251663 Ten Centuries Points: 1010 More actions · Answer 6

Ten Centuries

Points: 1010

July 22, 2009 at 11:15 am

thnxs guys, all is good!