SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


View Active Session SQL


View Active Session SQL

Author
Message
oradbguru
oradbguru
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 310
Comments posted to this topic are about the item View Active Session SQL
Ramu-459000
Ramu-459000
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 363
Hi --

I use the below query to get what is going on in the system now.. It shows the queries and sessions being run

select r.session_id,status,substring(qt.text,r.statement_start_offset/2, (case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else r.statement_end_offset end - r.statement_start_offset)/2) as query_text ,qt.dbid,qt.objectid,r.cpu_time,r.total_elapsed_time,r.reads,r.writes,r.logical_reads,r.scheduler_id from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(sql_handle) as qt where r.session_id > 50 order by r.scheduler_id, r.status, r.session_id

I posted this as this is a related topic.

Thanks
Steven.
Steven.
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1361 Visits: 247
Not tested completely, but this type of query can be written not to use a temp table or cursor
example below



DECLARE @spid SMALLINT
-- Set desired SPID. If NULL, it will return all non-system SPID's.
--SET @spid = <Put desired SPID her to filter>

SELECT
spid,
CASE blocked WHEN 0 THEN 'NO' ELSE 'YES' END blocked,
hostname,
db_name(dbid) AS dbName,
cmd,
(SELECT TEXT FROM ::fn_get_sql(sql_handle)) AS sqlText,
physical_io as phyIO,
status as Status,
program_name as programName,
login_time as loginTime,
last_batch as lastBatch
FROM sys.sysprocesses
WHERE
(ISNUMERIC(@spid) = 1 and spid > 50) or spid = @spid
ORDER BY blocked asc, spid




Steven
ericb1
ericb1
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 337
This looks like what I've been trying to show on my SQL server, but I'm getting an error:

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ' '.
Msg 137, Level 15, State 2, Line 35
Must declare the scalar variable "@desSPID".

I left the declare line commented out, assuming it would just return all processes? But that's not working. Tried adding "SET @desSPID = 2352" for my SQL process, but that gives the same error.

Any help is greatly appreciated, thanks!
patrickmcginnis59
patrickmcginnis59
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1101 Visits: 2333
ericb1 (2/24/2012)
This looks like what I've been trying to show on my SQL server, but I'm getting an error:

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ' '.
Msg 137, Level 15, State 2, Line 35
Must declare the scalar variable "@desSPID".

I left the declare line commented out, assuming it would just return all processes? But that's not working. Tried adding "SET @desSPID = 2352" for my SQL process, but that gives the same error.

Any help is greatly appreciated, thanks!


Maybe put the declare line back in, but leave out anything with 'Set @desSPID = whatever' as this will then leave the declared variable @desSPID with value NULL thus returning all processes.
oradbguru
oradbguru
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 310
You can try this one I published today in SS central:

http://www.sqlservercentral.com/scripts/Administration/88041/



SET NOCOUNT ON
GO

IF OBJECT_ID('tempdb..#curSQL') IS NOT NULL
DROP TABLE #curSQL
GO

CREATE TABLE #curSQL
(spID SMALLINT,
blocked VARCHAR(3),
hostName VARCHAR(2000),
dbName VARCHAR(100),
cmd VARCHAR(100),
sqlText NTEXT,
phyIO BIGINT,
Status VARCHAR(100),
programName VARCHAR(1000),
loginTime DATETIME,
lastBatch DATETIME
)

DECLARE @spID smallint,
        @Blocked VARCHAR(3),
        @sqltext VARBINARY(128),
        @physIO BIGINT,
        @hostName VARCHAR(2000),
        @desSPID SMALLINT,
        @dbName VARCHAR(100),
        @Cmd VARCHAR(100),
        @status VARCHAR(100),
        @programName VARCHAR(1000),
        @loginTime DATETIME,
        @lastBatch DATETIME
        

-- Set desired SPID. If NULL, it will return all non-system SPID's.
--SET @desSPID = <Put desired SPID her to filter>


BEGIN TRY
    IF @desSPID IS NOT NULL
        DECLARE spID_cursor CURSOR
        FORWARD_ONLY READ_ONLY
        FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES' END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
            FROM sys.sysprocesses
            WHERE spid = @desSPID
            GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
            ORDER BY spid

    ELSE
        DECLARE spID_cursor CURSOR
        FORWARD_ONLY READ_ONLY
        FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES' END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
            FROM sys.sysprocesses
            WHERE spid > 50
            GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
            ORDER BY spid
        OPEN spID_cursor

        FETCH NEXT
        FROM spID_cursor
        INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physIO, @status, @programName, @loginTime, @lastBatch

        WHILE @@FETCH_STATUS = 0
        BEGIN

         SELECT @sqltext = sql_handle
         FROM sys.sysprocesses
         WHERE spid = @spID

         INSERT INTO #curSQL
         SELECT @spID, @Blocked, @hostName, @dbName, @cmd, TEXT, @physIO, @status, @programName, @loginTime, @lastBatch
         FROM ::fn_get_sql(@sqltext)
        
         FETCH NEXT
         FROM spID_cursor
         INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physIO, @status, @programName, @loginTime, @lastBatch
        
         END
        
         CLOSE spID_cursor
         DEALLOCATE spID_cursor
    
    SELECT * FROM #curSQL ORDER BY blocked asc, spid
END TRY

BEGIN CATCH
    CLOSE spID_cursor
    DEALLOCATE spID_cursor
    
    SELECT ERROR_NUMBER() ErrorNBR, ERROR_SEVERITY() Severity, ERROR_LINE() ErrorLine, ERROR_MESSAGE() Msg
    
    PRINT 'There was an error in the script.'
    
END CATCH    


ericb1
ericb1
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 337
I get the same error unfortunately. Also, I'm running this as a script in a query window in SQL Server Management Studio. Do I need to execute it as a stored procedure? Or run it against a particular database?

Maybe I'm just executing it wrong?

thanks!
patrickmcginnis59
patrickmcginnis59
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1101 Visits: 2333
ericb1 (2/24/2012)
I get the same error unfortunately. Also, I'm running this as a script in a query window in SQL Server Management Studio. Do I need to execute it as a stored procedure? Or run it against a particular database?

Maybe I'm just executing it wrong?

thanks!


The original version had unicode stuff in it. Try pasting this one:


SET NOCOUNT ON
GO

IF OBJECT_ID('tempdb..#curSQL') IS NOT NULL
DROP TABLE #curSQL
GO

CREATE TABLE #curSQL
(spID SMALLINT,
blocked VARCHAR(3),
hostName VARCHAR(2000),
dbName VARCHAR(100),
cmd VARCHAR(100),
sqlText NTEXT,
phyIO BIGINT,
Status VARCHAR(100),
programName VARCHAR(1000),
loginTime DATETIME,
lastBatch DATETIME
)

DECLARE @spID smallint,
@Blocked VARCHAR(3),
@sqltext VARBINARY(128),
@physIO BIGINT,
@hostName VARCHAR(2000),
@desSPID SMALLINT,
@dbName VARCHAR(100),
@Cmd VARCHAR(100),
@status VARCHAR(100),
@programName VARCHAR(1000),
@loginTime DATETIME,
@lastBatch DATETIME


-- Set desired SPID. If NULL, it will return all non-system SPID's.
--SET @desSPID = <Put desired SPID her to filter>


BEGIN TRY
IF @desSPID IS NOT NULL
DECLARE spID_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES' END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
FROM sys.sysprocesses
WHERE spid = @desSPID
GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
ORDER BY spid

ELSE
DECLARE spID_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES' END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
FROM sys.sysprocesses
WHERE spid > 50
GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
ORDER BY spid
OPEN spID_cursor

FETCH NEXT
FROM spID_cursor
INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physIO, @status, @programName, @loginTime, @lastBatch

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = @spID

INSERT INTO #curSQL
SELECT @spID, @Blocked, @hostName, @dbName, @cmd, TEXT, @physIO, @status, @programName, @loginTime, @lastBatch
FROM ::fn_get_sql(@sqltext)

FETCH NEXT
FROM spID_cursor
INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physIO, @status, @programName, @loginTime, @lastBatch

END

CLOSE spID_cursor
DEALLOCATE spID_cursor

SELECT * FROM #curSQL ORDER BY blocked asc, spid
END TRY

BEGIN CATCH
CLOSE spID_cursor
DEALLOCATE spID_cursor

SELECT ERROR_NUMBER() ErrorNBR, ERROR_SEVERITY() Severity, ERROR_LINE() ErrorLine, ERROR_MESSAGE() Msg

PRINT 'There was an error in the script.'

END CATCH


ericb1
ericb1
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 337
Awesome! Thanks!
oradbguru
oradbguru
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 310
Here is another version that also gives you the lead blocker when blocking occurs. This is designed to be run in SSMS for SS 2005 thru 2008 r2.


SET NOCOUNT ON
GO

IF OBJECT_ID('tempdb..#curSQL') IS NOT NULL
DROP TABLE #curSQL
GO

CREATE TABLE #curSQL
(spID SMALLINT,
blocked VARCHAR(25),
hostName VARCHAR(2000),
dbName VARCHAR(100),
cmd VARCHAR(100),
sqlText NTEXT,
sqlcmd NTEXT,
phyIO BIGINT,
Status VARCHAR(100),
programName VARCHAR(1000),
loginTime DATETIME,
lastBatch DATETIME
)

DECLARE @spID smallint,
@Blocked VARCHAR(25),
@sqltext VARCHAR(max),
@sqlcmd VARCHAR(max),
@physIO BIGINT,
@hostName VARCHAR(2000),
@desSPID SMALLINT,
@dbName VARCHAR(100),
@Cmd VARCHAR(100),
@status VARCHAR(100),
@programName VARCHAR(1000),
@loginTime DATETIME,
@lastBatch DATETIME

-- Set desired SPID. If NULL, it will return all non-system SPID's.
-- SET @desSPID = 4704

BEGIN TRY
IF @desSPID IS NOT NULL
DECLARE spID_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES BY SPID: ' + CAST(blocked AS VARCHAR) END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
FROM sys.sysprocesses
WHERE spid = @desSPID
GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
ORDER BY spid

ELSE
DECLARE spID_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES BY SPID: ' + CAST(blocked AS VARCHAR) END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
FROM sys.sysprocesses
WHERE spid > 50 and spid <> @@SPID and status IN ('running', 'runnable', 'suspended')
GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
ORDER BY spid
OPEN spID_cursor

FETCH NEXT
FROM spID_cursor
INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physIO, @status, @programName, @loginTime, @lastBatch

WHILE @@FETCH_STATUS = 0
BEGIN

/*
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = @spID
*/
SELECT
@sqltext = A.text,
@sqlcmd = SUBSTRING(A.text, sp.stmt_start / 2,
(CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text)ELSE sp.stmt_end END - sp.stmt_start )/2)
FROM
sys.sysprocesses sp
OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) as A
WHERE
spid = @spID

INSERT INTO #curSQL
(spID, blocked, hostName, dbName, cmd, sqlText, sqlcmd, phyIO, Status, programName, loginTime, lastBatch)
VALUES(@spID, @Blocked, @hostName, @dbName, @cmd, @sqltext, @sqlcmd, @physIO, @status, @programName, @loginTime, @lastBatch)
-- SELECT @spID, @Blocked, @hostName, @dbName, @cmd, @sqltext, @sqlcmd, @physIO, @status, @programName, @loginTime, @lastBatch
--FROM ::fn_get_sql(@sqltext)

FETCH NEXT
FROM spID_cursor
INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physIO, @status, @programName, @loginTime, @lastBatch

END

CLOSE spID_cursor
DEALLOCATE spID_cursor

SELECT * FROM #curSQL ORDER BY blocked desc, spid

-- If there is blocking, print out blocking info.
IF (SELECT sum(blocked) from sys.sysprocesses WHERE spid > 50) > 0
--IF (SELECT TOP (1) COUNT(*) FROM #curSQL WHERE blocked = 'YES') > 0
BEGIN
/* SELECT x.session_id AS 'Blocked Session ID',
(
-- Query gets XML text for the sql query for the session_id
SELECT text AS [text()]
FROM sys.dm_exec_sql_text(x.sql_handle)
FOR XML PATH(''), TYPE

)AS 'Blocked SQL Text',
x.host_name,
x.login_name,
x.start_time,
x.totalReads,
x.totalWrites,
x.totalCPU,
x.writes_in_tempdb,
COALESCE(x.blocking_session_id, 0) AS 'Blocking Session ID',
(
SELECT p.text
FROM
(
-- Query gets the corresponding sql_handle info to find the XML text in the next query
SELECT MIN(sql_handle) AS sql_handle
FROM sys.dm_exec_requests r2
WHERE r2.session_id = x.blocking_session_id
) AS r_blocking
CROSS APPLY
(
-- Query will pull back the XML text for a blocking session if there is any from the sql_haldle
SELECT text AS [text()]
FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
FOR XML PATH(''), TYPE
) p (text)
) AS 'Blocking Text'
FROM
(
-- Query returns active session_id and metadata about the session for resource, blocking, and sql_handle
SELECT r.session_id,
s.host_name,
s.login_name,
r.start_time,
r.sql_handle,
r.blocking_session_id,
SUM(r.reads) AS totalReads,
SUM(r.writes) AS totalWrites,
SUM(r.cpu_time) AS totalCPU,
SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
--JOIN #curSQL cs ON r.session_id = cs.spID
WHERE r.status IN ('running', 'runnable', 'suspended') AND
r.blocking_session_id <> 0 AND
r.session_id <> @@SPID
GROUP BY r.session_id,
s.host_name,
s.login_name,
r.start_time,
r.sql_handle,
r.blocking_session_id
) x */

select
'LEAD BLOCKER: ' + CAST(spid as varchar(20)), loginame, cpu, memusage, physical_io, *
from
master..sysprocesses a
where
exists ( select b.*
from master..sysprocesses b
where b.blocked > 0 and
b.blocked = a.spid ) and not
exists ( select b.*
from master..sysprocesses b
where b.blocked > 0 and
b.spid = a.spid )
order by spid
END -- If there is blocking, print out blocking info.

END TRY

BEGIN CATCH
CLOSE spID_cursor
DEALLOCATE spID_cursor

SELECT ERROR_NUMBER() ErrorNBR, ERROR_SEVERITY() Severity, ERROR_LINE() ErrorLine, ERROR_MESSAGE() Msg

PRINT 'There was an error in the script.'

END CATCH


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search