Link to home
Start Free TrialLog in
Avatar of Viktor Wessel
Viktor Wessel

asked on

master.dbo.dba_GetSQLForSpid

while running this script

 -- Do not lock anything, and do not get held up by any locks.
   SET TRANSACTION ISOLATION LEVEL READ
      UNCOMMITTED
 

   -- If there are blocked processes...
   IF EXISTS(SELECT 1 FROM sys.sysprocesses WHERE
      blocked != 0)
   BEGIN
 

      -- Identify the root-blocking spid(s)
      SELECT  distinct t1.spid  AS [Root blocking spids]
         , t1.[loginame] AS [Owner]
         , master.dbo.dba_GetSQLForSpid(t1.spid) AS
            'SQL Text'
         , t1.[cpu]
         , t1.[physical_io]
         , DatabaseName = DB_NAME(t1.[dbid])
         , t1.[program_name]
         , t1.[hostname]
         , t1.[status]
         , t1.[cmd]
         , t1.[blocked]
         , t1.[ecid]
      FROM  sys.sysprocesses t1, sys.sysprocesses t2
      WHERE t1.spid = t2.blocked
        AND t1.ecid = t2.ecid
        AND t1.blocked = 0
      ORDER BY t1.spid, t1.ecid
 

      -- Identify the spids being blocked.
      SELECT t2.spid AS 'Blocked spid'
         , t2.blocked AS 'Blocked By'
         , t2.[loginame] AS [Owner]
         , master.dbo.dba_GetSQLForSpid(t2.spid) AS
            'SQL Text'
         , t2.[cpu]
         , t2.[physical_io]
         , DatabaseName = DB_NAME(t2.[dbid])
         , t2.[program_name]
         , t2.[hostname]
         , t2.[status]
         , t2.[cmd]
         , t2.ecid
      FROM sys.sysprocesses t1, sys.sysprocesses t2
      WHERE t1.spid = t2.blocked
        AND t1.ecid = t2.ecid
      ORDER BY t2.blocked, t2.spid, t2.ecid
   END

i'm getting an error

Cannot find either column "master" or the user-defined function or aggregate "master.dbo.dba_GetSQLForSpid", or the name is ambiguous.

what's the problem here?
Avatar of Viktor Wessel
Viktor Wessel

ASKER

Seems it doesn't work with SQL 2012
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Did you realize why isn't working?
I gave the explanation to it.
I have read your explanation, and while it looks reasonable, I don't think it's not a native function because of I have an instance of 2008 server on which I can guarantee I (and noone) didn't created such a function but it's there. So I believe it's some native function that was at 2008 but was removed at 2012.

Correct me if I'm wrong but it looks like this
It's very easy to proof that's not a native function:
  1. You can't find any reference to it in MSDN Books online
  2. None of native SQL Server functions start with dba_xxxxxxx
  3. You can alter it using ALTER FUNCTION dba_GetSQLForSpid

You can also get more information from the function by running the following code in the database:
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'

Open in new window

That's the correct explanation for the issue.