September 20, 2016 at 2:33 pm
Comments posted to this topic are about the item Identify tempdb usage
October 7, 2016 at 12:41 pm
David, Thank you for Sharing
I saw the use of master.dbo.fn_varbintohexstr
I like your approach better, even if Microsoft doesn't support it anymore (I still see it at SQL 2014)
Another Article Replacement for using fn_varbintohexstr or the use of the CONVERT
http://www.sqlservercentral.com/Forums/Topic1708816-391-1.aspx
Before, I was using this view to get details from TempDB
SELECT *
FROM (
SELECT TOP 100 PERCENT
spu.session_id
, db_name(r.database_id) dbname
, s.host_name host
, s.login_name loginname
, s.program_name [program_name]
, ISNULL(j.name, 'N/A') AS [Job Name]
, ( CASE WHEN ( CHARINDEX('SQLAgent', s.program_name) > 0 ) THEN REPLACE( SUBSTRING( s.program_name, 67, 10 ), ')', '')
ELSE 'N/A' END ) step
, r.start_time
, r.wait_time
, r.cpu_time
, r.logical_reads
, r.reads
, r.writes
, LTRIM( t.text ) text
FROM sys.dm_db_session_space_usage spu
JOIN sys.dm_exec_sessions s ON s.session_id = spu.session_id
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
INNER JOIN msdb.dbo.sysjobs j ON master.dbo.fn_varbintohexstr(CONVERT(varbinary(16), job_id)) = SUBSTRING(REPLACE(PROGRAM_NAME, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34)
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t,
( SELECT su.session_id, su.request_id
, SUM( su.internal_objects_alloc_page_count + su.user_objects_alloc_page_count) Task_Alloc
, SUM( su.internal_objects_dealloc_page_count + su.user_objects_dealloc_page_count) Task_Dealloc
FROM sys.dm_db_task_space_usage AS su
GROUP BY session_id, request_id ) AS su
WHERE r.status = 'running'
AND su.session_id = r.session_id
AND ( su.request_id = r.request_id )
AND ( s.session_id = su.session_id )
AND s.login_name NOT IN ('CORP\lmorales','corp\eRuhnke') -- Remove SA users
AND s.session_id <> ( SELECT @@SPID ) -- Eliminates current user session from results
AND su.session_id > 50 -- Sessions 50 and below are system sessions and should not be killed
AND s.program_name NOT IN ('SQL diagnostic manager Collection Service','SQL diagnostic manager Management Service')
) cia
There's still much to learn and improve.
Thank you.
October 10, 2016 at 8:59 am
What a cool script, thanks.
October 10, 2016 at 10:50 am
Happy to hear you are finding it useful.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy