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

1、解决 tempdb 中磁盘空间不足的问题解决 tempdb 中磁盘空间不足的问题SQL Server 2008 R2其他版本此主题尚未评级-评价此主题本主题提供了一些步骤和建议，可帮助您诊断和解决tempdb 数据库中磁盘空间不足导致的问题。如果tempdb中的磁盘空间用尽，可能会导致 SQL Server 生产环境受到严重破坏，并且可能会阻止正在运行的应用程序完成操作。tempdb 空间要求tempdb系统数据库是可供连接到 SQL Server 实例的所有用户使用的全局资源。tempdb数据库用于存储下列对象：用户对象、内部对象和版本存储区。您可以使用sys.dm_db_file_spac

2、e_usage动态管理视图监视tempdb文件中的用户对象、内部对象和版本存储区使用的磁盘空间。此外，若要在会话级或任务级监视tempdb中的页分配或页释放活动，可以使用动态管理视图sys.dm_db_session_space_usage和sys.dm_db_task_space_usage。这些视图可用于标识使用大量tempdb磁盘空间的大型查询、临时表或表变量。诊断 tempdb 磁盘空间问题下表列出了指示tempdb数据库中磁盘空间不足的错误消息。可以在 SQL Server 错误日志中找到这些错误，也可以将它们返回到任何正在运行的应用程序。错误引发错误的情况1101 或 1105任何

3、会话都必须分配tempdb中的空间。3959版本存储区已满。此错误在日志中通常出现在错误 1105 或 1101 之后。3967由于tempdb已满，版本存储区被强制收缩。3958 或 3966事务在tempdb中找不到所需的版本记录。数据库设置为自动增长且数据库大小快速增长时，也会指示出现tempdb磁盘空间问题。监视 tempdb 磁盘空间下列示例说明了如何确定tempdb中的可用空间量，以及如何确定版本存储区、内部对象和用户对象使用的空间量。确定 tempdb 中的可用空间量下面的查询将返回tempdb中所有文件的总可用页数和总可用空间量 (MB)。SELECT SUM(unalloca

4、ted_extent_page_count) AS free pages, (SUM(unallocated_extent_page_count)*1.0/128) AS free space in MBFROM 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.

5、0/128) AS version store space in MBFROM sys.dm_db_file_space_usage;确定运行时间最长的事务如果版本存储区使用了tempdb中的大量空间，则必须确定运行时间最长的事务。使用下面的查询可按顺序（事务的最长运行时间）列出活动事务。SELECT transaction_idFROM 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 i

7、n MBFROM sys.dm_db_file_space_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 MBFROM sys.dm_db_file_space_usage;确定总空间量（可用空间和已用空间）下面的查询将返回tempdb中所有文件使用的磁盘空间总量。

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

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

10、erver 代理作业所需的查询。A. 获取每个会话中当前运行的所有任务中的内部对象占用的空间下面的示例创建视图all_task_usage。执行查询后，视图将返回tempdb中当前运行的所有任务中的内部对象使用的总空间量。CREATE VIEW all_task_usageAS SELECT session_id, SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count, SUM(internal_objects_dealloc_page_count) AS task_internal_o

11、bjects_dealloc_page_count FROM sys.dm_db_task_space_usage GROUP BY session_id;GOB. 获取当前会话中正在运行的任务和已完成任务的内部对象占用的空间下面的示例创建视图all_session_usage。执行查询后，视图将返回tempdb中正在运行的任务和已完成任务中的所有内部对象使用的空间。CREATE VIEW all_session_usage AS SELECT R1.session_id, R1.internal_objects_alloc_page_count + R2.task_internal_obje

12、cts_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_session_space_usage AS R1 INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.sessio

13、n_id;GO假设查询这些视图的时间间隔为三分钟，结果集将提供下列信息。 在下午 5:00 时，会话 71 自会话开始后分配了 100 页并释放了 100 页。 在下午 5:03 时，会话 71 自会话开始后分配了 20100 页并释放了 100 页。分析此信息时，您可以由这两个度量得出以下结论：会话为内部对象分配了 20,000 页，并且没有释放任何页。这指示了存在一个潜在问题。注意作为数据库管理员，您可以决定将轮询的时间间隔改为小于三分钟。但是，如果查询运行时间少于三分钟，则该查询可能不会占用tempdb中的大量空间。若要确定在此期间运行的批处理，请使用SQL Server 事件探查器来捕

14、获RPC:Completed和SQL:BatchCompleted事件类。除了使用 SQL Server Profiler之外，还可以选择每三分钟为所有会话运行一次DBCC INPUTBUFFER，如下面的示例所示。DECLARE max int;DECLARE i int;SELECT max = max (session_id)FROM sys.dm_exec_sessionsSET i = 51 WHILE i = max BEGIN IF EXISTS (SELECT session_id FROM sys.dm_exec_sessions WHERE session_id=i) DB

15、CC INPUTBUFFER (i) SET i=i+1 END;方法 2：查询级信息有时，仅查看输入缓冲区或 SQL Server Profiler事件SQL:BatchCompleted并不能确定哪一查询使用了tempdb中的大多数磁盘空间。下列方法可用于查找此答案，但是这些方法要收集的数据比方法 1 中定义的过程多。若要继续使用此方法，请将SQL Server 代理作业设置为从动态管理视图sys.dm_db_task_space_usage轮询。与方法 1 相比，轮询间隔应该更短（每分钟一次）。使用这样短的间隔的原因为：如果当前未运行查询（任务），则sys.dm_db_task_spac

16、e_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_usageAS SELECT session_id, request_id, SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_c

17、ount, SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count FROM sys.dm_db_task_space_usage GROUP BY session_id, request_id;GOCREATE VIEW all_query_usageAS SELECT R1.session_id, R1.request_id, R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_

18、dealloc_page_count, R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle FROM all_request_usage R1 INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;GO如果查询计划位于缓存中，则可以随时检索查询的 Transact-SQL 文本和XML 显示计划格式的查询执行计划。若要获得已执行查询的

19、Transact-SQL 文本，请使用值sql_handle和动态管理函数sys.dm_exec_sql_text。若要获得查询执行计划，请使用值plan_handle和动态管理函数sys.dm_exec_query_plan。SELECT * FROM sys.dm_exec_sql_text(sql_handle);SELECT * FROM sys.dm_exec_query_plan(plan_handle);如果查询计划不在缓存中，则可以使用下列方法之一来获得查询的 Transact-SQL 文本和查询执行计划。A. 使用轮询方法从视图all_query_usage轮询，并运行下面的

20、查询以获得查询文本：SELECT R1.sql_handle, R2.text FROM all_query_usage AS R1OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2;由于sql_handle对每个唯一的批处理都应该是唯一的，因此不必保存重复的sql_handle项。若要保存计划句柄和 XML 计划，请运行下面的查询。SELECT R1.plan_handle, R2.query_plan FROM all_query_usage AS R1OUTER APPLY sys.dm_exec_query_plan(R1.pla

21、n_handle) AS R2;B. 使用 SQL Server 事件探查器事件除了轮询sys.dm_exec_sql_text和sys.dm_exec_query_plan函数之外，还可以使用 SQL Server Profiler事件。有一些事件探查器事件可用于捕获查询计划和生成的查询文本。例如，事件 165 将返回跟踪、SQL 文本、查询计划和查询统计信息的性能统计信息。监视临时表和表变量使用的空间可以使用一种类似于轮询查询的方法来监视临时表和临时变量使用的空间。在临时表或临时变量中获取大量用户数据的应用程序可能会导致tempdb的空间使用问题。这些表或变量属于用户对象。可以使用动态管理

22、视图sys.dm_db_session_space_usage中的user_objects_alloc_page_count和user_objects_dealloc_page_count列并按照前面介绍的方法进行操作。监视会话的页分配和页释放下表显示了动态管理视图sys.dm_db_file_space_usage、sys.dm_db_session_space_usage和sys.dm_db_task_space_usage返回的指定会话的结果。每行表示tempdb中一个指定会话的一次分配或释放活动。活动在“事件”列中列出。其他列显示动态管理视图的列中将返回的值。对于此情况，假设开始时te

23、mpdb数据库的未分配区中有 872 页，用户对象保留区中有 100 页。会话为一个用户表分配了 10 页，然后将它们全部释放。前 8 页位于混合区中。其余 2 页位于统一区中。事件dm_db_file_space_usageunallocated_extent_page_count 列dm_db_file_space_usageuser_object_reserved_page_count 列dm_db_session_space_usage和 dm_db_task_space_usageuser_object_alloc_page_count 列dm_db_session_space_usage和 dm_db_task_space_usageuser_object_dealloc_page_count 列开始87210000从现有混合区分配页 187210010分配页 2 到 8：占用一个新的混合区8648080分配页 9：占用一个新的统一区856108160从现有统一区分配页 10856108160从现有统一区释放页 10856108160释放页 9 和统一区864100168释放页 8864100169释放页 7 到 1，并在混合区释放8721001616请参阅