asked
FETCH CURSOR issue on tempdb
Recently we have face tempdb space issue when we monitored the opentans on tempdb we got the below fetch cursor running from more than one day and three sessions are running with same type of query and which are all sleeping state. We have killed those sessions and released the space to the tempdb. But we are in the process of configuring a monitoring scripts like when the tempdb usage reached 80% above we need to know what all sessions are occupying the tempdb and how much space they used and not released.(Including sleeping sessions). Do we have any such type monitoring scripts available please share here. The sleeping session query as below and we have killed them and released the space to drive. FETCH API_CURSOR000000000000053B
Hi, Joe Sack has done a nice write up over at [SQLSkills][1]. The scripts provided in the like should allow you to identify the actual TSQL associated with the SPID so you dont blindly kill it in the future. Let me know if it helps [1]:
http://www.sqlskills.com/blogs/joe/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch/
We have developed the script with our requirements. Maybe it will help to someone who are facing spaceissue with tempdb. SELECT TOP 10 session_id, ERQ.blocked, DB_NAME(ERQ.DBID) AS DBNAMES, ERQ.last_batch, user_objects_alloc_page_count, user_objects_dealloc_page_count, user_objects_alloc_page_count/128 AS user_objs_alloctotal_sizeMB, user_objects_dealloc_page_count/128 AS user_objs_dealloc_SizeMB, (user_objects_alloc_page_count - user_objects_dealloc_page_count)/128.0 AS user_objs_tobereleased_sizeMB, internal_objects_alloc_page_count, internal_objects_dealloc_page_count, internal_objects_alloc_page_count/128 AS internal_allocobjs_total_sizeMB, internal_objects_dealloc_page_count/128 AS internal_deallocobjs_SizeMB, (internal_objects_alloc_page_count - internal_objects_dealloc_page_count)/128.0 AS internal_objs_tobereleased_sizeMB, ERQ.status, EST.text FROM sys.dm_db_session_space_usage AS ses inner join sysprocesses ERQ WITH (NOLOCK) ON ses.session_id=ERQ.spid OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST WHERE ERQ.status='sleeping' ORDER BY ses.user_objects_alloc_page_count DESC go
18 People are following this question.
