Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2013
    Posts
    23

    Unanswered: Long running queries

    SQL Server 2012 Sp2 - Windows 2008R2

    Hi All,

    I have the following issue. We are detecting long running queries and sending the result to developers and us DBAs. I am trying to exclude the DBA run operations from going into the result meaning all items running under sysadmin privileges. I have the following result from part of the query...........As you can see, SPID are the same but loginame column has value in one row and not the others. This is Ok for the application account but I want to exclude this from the "MYDOM\sysadminacct" account. I have the following account but it also eliminates the blank/empty loginame columns from application account which I don't want. Can you please help me fix the query to eliminate only the "MYDOM\sysadminacct" account (including the empty/blank rows) from the query but not the application account and it's bank/empty rows ?

    SELECT spid, status,loginame,hostname,DB_NAME(dbid) as databasename,cmd,cpu,physical_io,last_batch,progra m_name
    FROM SYS.SYSPROCESSES
    WHERE spid in (select distinct spid from sys.sysprocesses) AND loginame <> ''
    AND loginame not in (Select loginname from sys.syslogins where sysadmin = 1) AND loginame <> 'NT AUTHORITY\SYSTEM'
    Attached Thumbnails Attached Thumbnails Untitled.png  

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You are picking up all of the ecids (the threads working on a parallel process). Instead of the sysproceses table, which is deprecated, if I recall, use sys.dm_exec_requests:
    Code:
    SELECT r.session_id, r.status, s.login_name, s.host_name, DB_NAME(r.database_id) as databasename, r.command, r.cpu_time, r.reads + r.writes as physical_io, r.start_time, s.program_name
    FROM SYS.dm_exec_requests r join
    sys.dm_exec_sessions s on r.session_id = s.session_id
    WHERE r.session_id > 50 -- not a system process
      AND is_srvrolemember ('sysadmin', s.login_name) = 0
      AND s.login_name <> 'NT AUTHORITY\SYSTEM'

  3. #3
    Join Date
    Jul 2013
    Posts
    23
    Quote Originally Posted by MCrowley View Post
    You are picking up all of the ecids (the threads working on a parallel process). Instead of the sysproceses table, which is deprecated, if I recall, use sys.dm_exec_requests:
    Code:
    SELECT r.session_id, r.status, s.login_name, s.host_name, DB_NAME(r.database_id) as databasename, r.command, r.cpu_time, r.reads + r.writes as physical_io, r.start_time, s.program_name
    FROM SYS.dm_exec_requests r join
    sys.dm_exec_sessions s on r.session_id = s.session_id
    WHERE r.session_id > 50 -- not a system process
      AND is_srvrolemember ('sysadmin', s.login_name) = 0
      AND s.login_name <> 'NT AUTHORITY\SYSTEM'

    Thanks for the reply.

    "WHERE r.session_id > 50" -----------> This is not valid. See http://sqlblog.com/blogs/adam_machan...r-process.aspx

  4. #4
    Join Date
    Oct 2014
    Posts
    1
    jhfjkfhjkfyairu jhfjkhfajklsdruaieouriowu ihjrkljhfilaseuroier

    __________
    adil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •