解决 tempdb 中磁盘空间不足的问题.docx

1、 解决解决 tempdb 中磁盘空间不足的问题中磁盘空间不足的问题 解决解决 tempdb 中磁盘空间不足的问题中磁盘空间不足的问题 tempdb 空间要求 tempdb 系统数据库是可供连接到 SQL Server 实例的所有用户使用的全局资源。tempdb 数据库用于存储下列对象：用户对象、内部对象和版本存储区。您可以使用 sys.dm_db_file_space_usage 动态管理视图监视 tempdb 文件中的用户对象、内部对象和版本存储区使用的磁盘空间。此外，若要在会话级或任务级监视 tempdb 中的页分配或页释放活动，可以使用动态管理视图 sys.dm_db_session_s

2、pace_usage 和 sys.dm_db_task_space_usage。这些视图可用于标识使用大量 tempdb 磁盘空间的大型查询、临时表或表变量。诊断 tempdb 磁盘空间问题 下表列出了指示 tempdb 数据库中磁盘空间不足的错误消息。可以在 SQL Server 错误日志中找到这些错误，也可以将它们返回到任何正在运行的应用程序。错误 引发错误的情况 1101 或 1105 任何会话都必须分配 tempdb 中的空间。3959 版本存储区已满。此错误在日志中通常出现在错误 1105 或 1101 之后。3967 由于 tempdb 已满，版本存储区被强制收缩。3958 或 3

3、966 事务在 tempdb 中找不到所需的版本记录。数据库设置为自动增长且数据库大小快速增长时，也会指示出现 tempdb 磁盘空间问题。监视 tempdb 磁盘空间 下列示例说明了如何确定 tempdb 中的可用空间量，以及如何确定版本存储区、内部对象和用户对象使用的空间量。确定确定 tempdb 中的可用空间量中的可用空间量 下面的查询将返回 tempdb 中所有文件的总可用页数和总可用空间量(MB)。复制 SELECT SUM(unallocated_extent_page_count)AS free pages,(SUM(unallocated_extent_page_count)*

4、1.0/128)AS free space in MB FROM sys.dm_db_file_space_usage;确定版本存储区使用的空间量确定版本存储区使用的空间量 下面的查询将返回 tempdb 中版本存储区使用的总页数和总空间量(MB)。复制 SELECT SUM(version_store_reserved_page_count)AS version store pages used,(SUM(version_store_reserved_page_count)*1.0/128)AS version store space in MB FROM sys.dm_db_file_sp

5、ace_usage;确定运行时间最长的事务确定运行时间最长的事务 如果版本存储区使用了 tempdb 中的大量空间，则必须确定运行时间最长的事务。使用下面的查询可按顺序（事务的最长运行时间）列出活动事务。SELECT transaction_id FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;与联机索引操作无关的长时间运行的事务需要很大的版本存储区。此版本存储区保存自事务启动以来生成的所有版本。联机索引生成事务可能需要较长时间才能完成，但是使用了专用于联机索引操

6、作的单独的版本存储区。因此，这些操作不会防止删除其他事务的版本。有关详细信息，请参阅行版本控制资源的使用情况。确定内部对象使用的空间量确定内部对象使用的空间量 下面的查询将返回 tempdb 中内部对象使用的总页数和总空间量(MB)。SELECT SUM(internal_object_reserved_page_count)AS internal object pages used,(SUM(internal_object_reserved_page_count)*1.0/128)AS internal object space in MB FROM sys.dm_db_file_space

7、_usage;确定用户对象使用的空间量确定用户对象使用的空间量 下面的查询将返回 tempdb 中用户对象使用的总页数和总空间量。SELECT SUM(user_object_reserved_page_count)AS user object pages used,(SUM(user_object_reserved_page_count)*1.0/128)AS user object space in MB FROM sys.dm_db_file_space_usage;确定总空间量（可用空间和已用空间）确定总空间量（可用空间和已用空间）下面的查询将返回 tempdb 中所有文件使用的磁盘空

8、间总量。SELECT SUM(size)*1.0/128 AS size in MB FROM tempdb.sys.database_files 监视查询使用的空间 最常见的 tempdb 空间使用量问题中，有一种与使用大量空间的大型查询相关联。通常，此空间用于内部对象，例如工作表或工作文件。虽然监视内部对象使用的空间可以使您了解空间的使用情况，但不会直接标识出使用该空间的查询。下列方法可帮助您标识出使用了 tempdb 中的大多数空间的查询。第一种方法是检查批处理级数据，此方法比第二种方法使用的数据少。第二种方法可用于标识占用磁盘空间的特定查询、临时表或表变量，但要获得答案必须收集更多数据

9、。方法方法 1：批处理级信息：批处理级信息 如果批处理请求只包含少量查询，并且其中只有一个查询是复杂查询，则此信息通常仅能确定占用空间的批处理，而无法确定特定的查询。若要继续使用此方法，必须通过使用大约几分钟的轮询间隔，将 SQL Server 代理作业设置为从动态管理视图 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage 轮询。下面的示例使用了三分钟的轮询间隔。由于 sys.dm_db_session_space_usage 不包括当前活动任务的分配活动，因此必须从两个视图轮询。通过比较在两个时间间隔分配的页数之差，可以计算

10、出在这两个间隔之间分配的页数。下列示例提供了 SQL Server 代理作业所需的查询。A.获取每个会话中当前运行的所有任务中的内部对象占用的空间获取每个会话中当前运行的所有任务中的内部对象占用的空间 下面的示例创建视图 all_task_usage。执行查询后，视图将返回 tempdb 中当前运行的所有任务中的内部对象使用的总空间量。CREATE VIEW all_task_usage AS SELECT session_id,SUM(internal_objects_alloc_page_count)AS task_internal_objects_alloc_page_count,SUM

11、(internal_objects_dealloc_page_count)AS task_internal_objects_dealloc_page_count FROM sys.dm_db_task_space_usage GROUP BY session_id;GO B.获取当前会话中正在运行的任务和已完成任务的内部对象获取当前会话中正在运行的任务和已完成任务的内部对象占用的空间占用的空间 下面的示例创建视图 all_session_usage。执行查询后，视图将返回 tempdb 中正在运行的任务和已完成任务中的所有内部对象使用的空间。CREATE VIEW all_session_us

12、age AS SELECT R1.session_id,R1.internal_objects_alloc_page_count +R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,R1.internal_objects_dealloc_page_count +R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count FROM sys.dm_db_s

13、ession_space_usage AS R1 INNER JOIN all_task_usage AS R2 ON R1.session_id=R2.session_id;GO 假设查询这些视图的时间间隔为三分钟，结果集将提供下列信息。在下午 5:00 时，会话 71 自会话开始后分配了 100 页并释放了 100 页。在下午 5:03 时，会话 71 自会话开始后分配了 20100 页并释放了 100 页。分析此信息时，您可以由这两个度量得出以下结论：会话为内部对象分配了 20,000 页，并且没有释放任何页。这指示了存在一个潜在问题。注意 作为数据库管理员，您可以决定将轮询的时间间隔改

14、为小于三分钟。但是，如果查询运行时间少于三分钟，则该查询可能不会占用 tempdb 中的大量空间。若要确定在此期间运行的批处理，请使用 SQL Server 事件探查器来捕获 RPC:Completed 和 SQL:BatchCompleted 事件类。除了使用 SQL Server Profiler 之外，还可以选择每三分钟为所有会话运行一次 DBCC INPUTBUFFER，如下面的示例所示。DECLARE max int;DECLARE i int;SELECT max=max(session_id)FROM sys.dm_exec_sessions SET i=51 WHILE i=m

15、ax BEGIN IF EXISTS(SELECT session_id FROM sys.dm_exec_sessions WHERE session_id=i)DBCC INPUTBUFFER(i)SET i=i+1 END;方法方法 2：查询级信息：查询级信息 有时，仅查看输入缓冲区或 SQL Server Profiler 事件 SQL:BatchCompleted 并不能确定哪一查询使用了 tempdb 中的大多数磁盘空间。下列方法可用于查找此答案，但是这些方法要收集的数据比方法 1 中定义的过程多。若要继续使用此方法，请将 SQL Server 代理作业设置为从动态管理视图 sys

16、.dm_db_task_space_usage 轮询。与方法 1 相比，轮询间隔应该更短（每分钟一次）。使用这样短的间隔的原因为：如果当前未运行查询（任务），则 sys.dm_db_task_space_usage 不会返回数据。在轮询查询中，将动态管理视图 sys.dm_db_task_space_usage 上定义的视图与 sys.dm_exec_requests 联接在一起，以返回 sql_handle、statement_start_offset、statement_end_offset 和 plan_handle 列。CREATE VIEW all_request_usage AS SELECT session_id,request_id,SUM(internal_objects_alloc_page_count)AS request_internal_objects_alloc_page_count,SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count F