由于执行ETL包，TempDB大小正在增加

问题描述

我们如何找出哪个ETL包执行负责增加SSIS数据库大小？

How can we find out the which ETL package execution is responsible for increase in SSIS Database size ?

请建议方法或查询以找出原因或根本原因

Please suggest the method or query to find out the reason or root cause

提前致谢

Neeraj Dubey

Neeraj Dubey

推荐答案

你的意思是增加tempdb大小，不是吗？

You mean increase in tempdb size, don't you?

这个查询应该为你提供每个会话的分配：

This query should give you allocations per session:

SELECT
  sys.dm_exec_sessions.session_id AS [SESSION ID]
  ,DB_NAME(database_id) AS [DATABASE Name]
  ,HOST_NAME AS [System Name]
  ,program_name AS [Program Name]
  ,login_name AS [USER Name]
  ,status
  ,cpu_time AS [CPU TIME (in milisec)]
  ,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
  ,total_elapsed_time AS    [Elapsed TIME (in milisec)]
  ,(memory_usage * 8)      AS [Memory USAGE (in KB)]
  ,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
  ,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
  ,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
  ,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
  ,CASE is_user_process
             WHEN 1      THEN 'user session'
             WHEN 0      THEN 'system session'
  END         AS [SESSION Type], row_count AS [ROW COUNT]
FROM
  sys.dm_db_session_space_usage
INNER join
  sys.dm_exec_sessions
ON  sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

希望有所帮助，

这篇关于由于执行ETL包，TempDB大小正在增加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！