SQL Server Training | Tutorials | How to's

SQL Script to find out 5 biggest transaction that uses internal objects

-- ******************************************** */
-- Description                                  */
-- ============                                 */
-- Script to check the biggest transaction      */
-- that are using internal objects              */
-- If it is for internal objects (tempdb        */
-- issues), use the script bellow. It lists     */
-- the top 5 sessions.                          */
-- =============================================*/
-- Compatibility  :     2005+                   */
-- ******************************************** */
SELECT top 5 * 
FROM sys.dm_db_task_space_usage
ORDER BY (user_objects_alloc_page_count 
  + internal_objects_alloc_page_count) desc
go