To know what are the queries are running

• prakash.gr

  SSC-Addicted

  Points: 428

  More actions

  November 30, 2008 at 7:44 am

  #72308

  Hi,

  server version:sql server 2000;

  i am oracle dba and in oracle lot of views are there to know what are the queries are running for particular time similarly in sql server i want to know.

  how i can check what are the present sql queries are running in sql server with full queries with other information like spid, when he has connected...

  Thanks

  Prakash

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 30, 2008 at 9:33 am

  #905259

  You can query the sysprocesses system table.

  As with many of the system tables, it's well documented in Books Online, including what each of the columns mean.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• prakash.gr

  SSC-Addicted

  Points: 428

  More actions

  November 30, 2008 at 10:25 am

  #905265

  Hi Gail Shaw,

  in the sysprocesses system table i won't get full text of sql statemets currntly running. i want full text of sql statements so any method to get those statements.

  Thanks

  Prakash

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 30, 2008 at 10:42 am

  #905266

  Use either DBCC INPUTBUFFER or fn_get_sql in conjunction with sysprocesses. There's no simple, easy way of getting the commands on SQL 2000 though.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• prakash.gr

  SSC-Addicted

  Points: 428

  More actions

  November 30, 2008 at 11:00 am

  #905271

  Hi Gail Shaw,

  Thanks for your inputs.

  Cheers

  Prakash

• Vee

  Hall of Fame

  Points: 3028

  More actions

  December 1, 2008 at 7:30 am

  #905624

  why dont you try this:?

  SET NOCOUNT ON

  DECLARE @Handle varbinary(64);

  DECLARE @SPID INT;

  declare @SpidStatus varchar(100),@LoginName varchar(100), @HostName char(250),@DBname sysname,@CTEXT VARCHAR(8000)

  DECLARE [Spids_Cursor] CURSOR FOR

  select spid, db_name(dbid) as dbname,loginame,hostname from master..sysprocesses where spid>50 and spid<>@@spid

  FOR READ ONLY

  OPEN [Spids_Cursor]

  WHILE 1 = 1

  BEGIN

  FETCH NEXT FROM [Spids_Cursor] INTO @Spid, @DBname,@LoginName,@HostName

  IF @@fetch_status <> 0 BREAK

  SELECT @Handle = sql_handle

  FROM master..sysprocesses

  WHERE spid = @Spid

  SELECT @CTEXT=TEXT FROM ::fn_get_sql(@Handle);

  IF @CTEXT IS NULL OR LEN(LTRIM(RTRIM(@CTEXT)))=0

  BEGIN

  Select 'Spid : '+str(@Spid)

  Select 'DatabaseName : '+ @DBname

  select 'Login : ' + @LoginName

  SELECT 'HostName: '+ @HostName

  SELECT 'Command Exeuted:'

  DBCC INPUTBUFFER(@Spid)

  END

  ELSE

  Select 'Spid : '+str(@Spid)

  Select 'DatabaseName : '+ @DBname

  select 'Login : ' + @LoginName

  SELECT 'HostName: '+ @HostName

  SELECT 'Command Exeuted:' + @CTEXT

  END

  deallocate [Spids_Cursor]

• prakash.gr

  SSC-Addicted

  Points: 428

  More actions

  December 1, 2008 at 8:02 am

  #905645

  Hi SSC Veteran,

  Thx for your suggestions.

  Thanks

  Prakash