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

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

《解决 tempdb 中磁盘空间不足的问题.docx》由会员分享，可在线阅读，更多相关《解决 tempdb 中磁盘空间不足的问题.docx（9页珍藏版）》请在冰豆网上搜索。

解决解决tempdb中磁盘空间不足的问题中磁盘空间不足的问题解决解决tempdb中磁盘空间不足的问题中磁盘空间不足的问题tempdb空间要求tempdb系统数据库是可供连接到SQLServer实例的所有用户使用的全局资源。

tempdb数据库用于存储下列对象：

用户对象、内部对象和版本存储区。

您可以使用sys.dm_db_file_space_usage动态管理视图监视tempdb文件中的用户对象、内部对象和版本存储区使用的磁盘空间。

此外，若要在会话级或任务级监视tempdb中的页分配或页释放活动，可以使用动态管理视图sys.dm_db_session_space_usage和sys.dm_db_task_space_usage。

这些视图可用于标识使用大量tempdb磁盘空间的大型查询、临时表或表变量。

诊断tempdb磁盘空间问题下表列出了指示tempdb数据库中磁盘空间不足的错误消息。

可以在SQLServer错误日志中找到这些错误，也可以将它们返回到任何正在运行的应用程序。

错误引发错误的情况1101或1105任何会话都必须分配tempdb中的空间。

3959版本存储区已满。

此错误在日志中通常出现在错误1105或1101之后。

3967由于tempdb已满，版本存储区被强制收缩。

3958或3966事务在tempdb中找不到所需的版本记录。

数据库设置为自动增长且数据库大小快速增长时，也会指示出现tempdb磁盘空间问题。

监视tempdb磁盘空间下列示例说明了如何确定tempdb中的可用空间量，以及如何确定版本存储区、内部对象和用户对象使用的空间量。

确定确定tempdb中的可用空间量中的可用空间量下面的查询将返回tempdb中所有文件的总可用页数和总可用空间量（MB）。

复制SELECTSUM（unallocated_extent_page_count）ASfreepages,（SUM（unallocated_extent_page_count）*1.0/128）ASfreespaceinMBFROMsys.dm_db_file_space_usage;确定版本存储区使用的空间量确定版本存储区使用的空间量下面的查询将返回tempdb中版本存储区使用的总页数和总空间量（MB）。

复制SELECTSUM（version_store_reserved_page_count）ASversionstorepagesused,（SUM（version_store_reserved_page_count）*1.0/128）ASversionstorespaceinMBFROMsys.dm_db_file_space_usage;确定运行时间最长的事务确定运行时间最长的事务如果版本存储区使用了tempdb中的大量空间，则必须确定运行时间最长的事务。

使用下面的查询可按顺序（事务的最长运行时间）列出活动事务。

SELECTtransaction_idFROMsys.dm_tran_active_snapshot_database_transactionsORDERBYelapsed_time_secondsDESC;与联机索引操作无关的长时间运行的事务需要很大的版本存储区。

此版本存储区保存自事务启动以来生成的所有版本。

联机索引生成事务可能需要较长时间才能完成，但是使用了专用于联机索引操作的单独的版本存储区。

因此，这些操作不会防止删除其他事务的版本。

有关详细信息，请参阅行版本控制资源的使用情况。

确定内部对象使用的空间量确定内部对象使用的空间量下面的查询将返回tempdb中内部对象使用的总页数和总空间量（MB）。

SELECTSUM（internal_object_reserved_page_count）ASinternalobjectpagesused,（SUM（internal_object_reserved_page_count）*1.0/128）ASinternalobjectspaceinMBFROMsys.dm_db_file_space_usage;确定用户对象使用的空间量确定用户对象使用的空间量下面的查询将返回tempdb中用户对象使用的总页数和总空间量。

SELECTSUM（user_object_reserved_page_count）ASuserobjectpagesused,（SUM（user_object_reserved_page_count）*1.0/128）ASuserobjectspaceinMBFROMsys.dm_db_file_space_usage;确定总空间量（可用空间和已用空间）确定总空间量（可用空间和已用空间）下面的查询将返回tempdb中所有文件使用的磁盘空间总量。

SELECTSUM（size）*1.0/128ASsizeinMBFROMtempdb.sys.database_files监视查询使用的空间最常见的tempdb空间使用量问题中，有一种与使用大量空间的大型查询相关联。

通常，此空间用于内部对象，例如工作表或工作文件。

虽然监视内部对象使用的空间可以使您了解空间的使用情况，但不会直接标识出使用该空间的查询。

下列方法可帮助您标识出使用了tempdb中的大多数空间的查询。

第一种方法是检查批处理级数据，此方法比第二种方法使用的数据少。

第二种方法可用于标识占用磁盘空间的特定查询、临时表或表变量，但要获得答案必须收集更多数据。

方法方法1：

批处理级信息：

批处理级信息如果批处理请求只包含少量查询，并且其中只有一个查询是复杂查询，则此信息通常仅能确定占用空间的批处理，而无法确定特定的查询。

若要继续使用此方法，必须通过使用大约几分钟的轮询间隔，将SQLServer代理作业设置为从动态管理视图sys.dm_db_session_space_usage和sys.dm_db_task_space_usage轮询。

下面的示例使用了三分钟的轮询间隔。

由于sys.dm_db_session_space_usage不包括当前活动任务的分配活动，因此必须从两个视图轮询。

通过比较在两个时间间隔分配的页数之差，可以计算出在这两个间隔之间分配的页数。

下列示例提供了SQLServer代理作业所需的查询。

A.获取每个会话中当前运行的所有任务中的内部对象占用的空间获取每个会话中当前运行的所有任务中的内部对象占用的空间下面的示例创建视图all_task_usage。

执行查询后，视图将返回tempdb中当前运行的所有任务中的内部对象使用的总空间量。

CREATEVIEWall_task_usageASSELECTsession_id,SUM（internal_objects_alloc_page_count）AStask_internal_objects_alloc_page_count,SUM（internal_objects_dealloc_page_count）AStask_internal_objects_dealloc_page_countFROMsys.dm_db_task_space_usageGROUPBYsession_id;GOB.获取当前会话中正在运行的任务和已完成任务的内部对象获取当前会话中正在运行的任务和已完成任务的内部对象占用的空间占用的空间下面的示例创建视图all_session_usage。

执行查询后，视图将返回tempdb中正在运行的任务和已完成任务中的所有内部对象使用的空间。

CREATEVIEWall_session_usageASSELECTR1.session_id,R1.internal_objects_alloc_page_count+R2.task_internal_objects_alloc_page_countASsession_internal_objects_alloc_page_count,R1.internal_objects_dealloc_page_count+R2.task_internal_objects_dealloc_page_countASsession_internal_objects_dealloc_page_countFROMsys.dm_db_session_space_usageASR1INNERJOINall_task_usageASR2ONR1.session_id=R2.session_id;GO假设查询这些视图的时间间隔为三分钟，结果集将提供下列信息。

在下午5:

00时，会话71自会话开始后分配了100页并释放了100页。

在下午5:

03时，会话71自会话开始后分配了20100页并释放了100页。

分析此信息时，您可以由这两个度量得出以下结论：

会话为内部对象分配了20,000页，并且没有释放任何页。

这指示了存在一个潜在问题。

注意作为数据库管理员，您可以决定将轮询的时间间隔改为小于三分钟。

但是，如果查询运行时间少于三分钟，则该查询可能不会占用tempdb中的大量空间。

若要确定在此期间运行的批处理，请使用SQLServer事件探查器来捕获RPC:

Completed和SQL:

BatchCompleted事件类。

除了使用SQLServerProfiler之外，还可以选择每三分钟为所有会话运行一次DBCCINPUTBUFFER，如下面的示例所示。

DECLAREmaxint;DECLAREiint;SELECTmax=max（session_id）FROMsys.dm_exec_sessionsSETi=51WHILEi=maxBEGINIFEXISTS（SELECTsession_idFROMsys.dm_exec_sessionsWHEREsession_id=i）DBCCINPUTBUFFER（i）SETi=i+1END;方法方法2：

查询级信息：

查询级信息有时，仅查看输入缓冲区或SQLServerProfiler事件SQL:

BatchCompleted并不能确定哪一查询使用了tempdb中的大多数磁盘空间。

下列方法可用于查找此答案，但是这些方法要收集的数据比方法1中定义的过程多。

若要继续使用此方法，请将SQLServer代理作业设置为从动态管理视图sys.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列。

CREATEVIEWall_request_usageASSELECTsession_id,request_id,SUM（internal_objects_alloc_page_count）ASrequest_internal_objects_alloc_page_count,SUM（internal_objects_dealloc_page_count）ASrequest_internal_objects_dealloc_page_countF