Monitoring processes

• Emilio.Mendoza

  Mr or Mrs. 500

  Points: 576

  More actions

  October 8, 2008 at 10:01 am

  #94143

  I am trying to catch a process wich is doing an insert and everytime it is running the server CPU spikes to 99% and won't allow connections with EM neither with terminal services. We workaround this problem and restart the server to 'fix' it, after the reboot I was able to see the process and detect the sentence is running.

  So far so good, the problem is that I can only see the first 255 characthers of that sentence, and I need to see the whole code so I can send it to the developers so they can review it.

  At first I used the DBCC BUFFERINPUT (SPID) to obtain the code but it is limited to the first 255 chars.

  I found the next script:

  DECLARE @handle binary(20)

  SELECT @handle = sql_handle

  FROM master..sysprocesses

  WHERE spid = @@SPID

  SELECT [text]

  FROM ::fn_get_sql(@handle)

  Unfortunately I am SOL since I can get over those 255 chars.

  Is there a way to obtain the whole sentence for that process??

  Thanks in advance

• Herve Roggero

  Ten Centuries

  Points: 1230

  More actions

  October 15, 2008 at 1:40 pm

  #885455

  I have not tried this with those statements, but have you considered changing the default column width? In option -> Results (query analyzer), you can change the characters per column (defaults to 255). Try setting this value higher and see if you get more characters back.

  In fact... I am interested to know the result... 🙂

  Hope that works for you.

  Thanks

  Herve Roggero
  hroggero@pynlogic.com
  MCDBA, MCSE, MCSD
  SQL Server Database Proxy/Firewall and Auditing

• Jeff Moden

  SSC Guru

  Points: 1004638

  More actions

  October 15, 2008 at 6:58 pm

  #885597

  According to the following...

  http://msdn.microsoft.com/en-us/library/aa258866(SQL.80).aspx

  ... the Test returned is of the data type TEXT which can contain up to 2 billion characters. But, the largest "column width" you can set query analyzer for is 8060 so you'll never see more than that.

  Of course, if the code had a "purpose" header, you'd only need the first couple of hundred to find out which proc it was. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)