在SQL Server tempdb满时检查数据文件

日期： 2009-04-28 作者：Matthew Schroeder翻译：April 来源：TechTarget中国 英文

作为一名数据库DBA，肯定会听说过“tempdb数据库满了”。通常我们很容易确定造成这一问题的原因。但是更多的时候这一问题主要源于一组请求，涉及到新代码部署或逐渐增加的数据。 　　“Tempdb满了”意味着什么？ 　　当SQL Server tempdb满了时，上层管理常常需要决策、一些开发人员可能会推卸责任，就连高级DBA也害怕碰到这种情况。

　　和我告诉管理员的一样，首先经验的做法就是：保持冷静。不要让还没有公布的情况给其他方面造成压力，那样可能酿成更大的错误。 　　既然情况已经出现了，那我们就来解决问题。Tempdb数据库由两部分组成：一是原始文件组里的数据文件，二是tempdb日志文件……

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

作为一名数据库DBA，肯定会听说过“tempdb数据库满了”。通常我们很容易确定造成这一问题的原因。但是更多的时候这一问题主要源于一组请求，涉及到新代码部署或逐渐增加的数据。

　　“Tempdb满了”意味着什么？

　　当SQL Server tempdb满了时，上层管理常常需要决策、一些开发人员可能会推卸责任，就连高级DBA也害怕碰到这种情况。

　　和我告诉管理员的一样，首先经验的做法就是：保持冷静。不要让还没有公布的情况给其他方面造成压力，那样可能酿成更大的错误。

　　既然情况已经出现了，那我们就来解决问题。Tempdb数据库由两部分组成：一是原始文件组里的数据文件，二是tempdb日志文件。这两者都可能出错，但错误信息会告诉你哪一部分满了。首先我们一起看看数据文件部分。在以后的文章部分中再讲解日志文件。

　　我们怎么压缩源文件？
　
　　首先我们要了解一下确定是什么占用大部分空间的方法，哪一个服务器有我们处理的ID号（SPID）、请求是从哪一台主机上发出的。以下查询将返回数据库里占空间的前1000个SPID。记住这些返回的值为页码数。为此，我算了一下存储值（单位为MB）。同样，我们还要注意计数器是随着SPID的使用时间而逐渐积累的：


SELECT top 1000
    s.host_name, su.[session_id], d.name [DBName], su.[database_id],
    su.[user_objects_alloc_page_count] [Usr_Pg_Alloc], su.[user_objects_dealloc_page_count] [Usr_Pg_DeAlloc],
    su.[internal_objects_alloc_page_count] [Int_Pg_Alloc], su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc],
    (su.[user_objects_alloc_page_count]*1.0/128) [Usr_Alloc_MB], (su.[user_objects_dealloc_page_count]*1.0/128)
    [Usr_DeAlloc_MB],
    (su.[internal_objects_alloc_page_count]*1.0/128) [Int_Alloc_MB], (su.[internal_objects_dealloc_page_count]*1.0/128)
    [Int_DeAlloc_MB]
    FROM [sys].[dm_db_session_space_usage] su
    inner join sys.databases d on su.database_id = d.database_id
    inner join sys.dm_exec_sessions s on su.session_id = s.session_id
    where (su.user_objects_alloc_page_count > 0 or
    su.internal_objects_alloc_page_count > 0)
    order by case when su.user_objects_alloc_page_count > su.internal_objects_alloc_page_count then
su.user_objects_alloc_page_count else su.internal_objects_alloc_page_count end desc

　　第二个查询也非常类似，它返回的是SPID给分配空间的前1000条。该查询能跟踪可以循环、创建项目或运行时创建、删除多个临时对象的程序。


 SELECT top 1000 s.host_name, su.[session_id], d.name [DBName], su.[database_id],
    su.[user_objects_alloc_page_count] [Usr_Pg_Alloc], su.[user_objects_dealloc_page_count] [Usr_Pg_DeAlloc],
    su.[internal_objects_alloc_page_count] [Int_Pg_Alloc], su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc],
    (su.[user_objects_alloc_page_count]*1.0/128) [Usr_Alloc_MB], (su.[user_objects_dealloc_page_count]*1.0/128)
    [Usr_DeAlloc_MB],
    (su.[internal_objects_alloc_page_count]*1.0/128) [Int_Alloc_MB], (su.[internal_objects_dealloc_page_count]*1.0/128)
    [Int_DeAlloc_MB]
    FROM [sys].[dm_db_session_space_usage] su
    inner join sys.databases d on su.database_id = d.database_id
    inner join sys.dm_exec_sessions s on su.session_id = s.session_id
    where (su.user_objects_dealloc_page_count > 0 or
    su.internal_objects_dealloc_page_count > 0)
    order by case when su.user_objects_dealloc_page_count > su.internal_objects_dealloc_page_count then
    su.user_objects_dealloc_page_count else su.internal_objects_dealloc_page_count end desc

　　由于tempdb在压缩后没有报告它的大小，以下查询可以提供tempdb里的有用空间。


  SELECT sum(unallocated_extent_page_count) [Free_Pages],
    (sum(unallocated_extent_page_count)*1.0/128) [Free_Space_MB]
    FROM sys.dm_db_file_space_usage

　　如果你已经决定了SPID，你就可以决定用dbcc缓冲器（SPID）运行什么样的T-SQL。

　　假设你清楚运行的T-SQL代码，但是你还需要知道会牵涉到的临时表。你可以执行以下程序：

select * from tempdb.sys.objects where type = 'u'

　　临时表源于T-SQL里那些应该有#YourDefinedTblName____UniqueID格式的用户。它能帮你识别涉及到的代码。你还可以用sys.dm_exec_requests命令联结SPID、用sys.dm_exec_sql_text(SQL_Handle)获取当时运行的命令，但要求脚本在实际运行时用“polling loop”监控。

　　小结

　　在现有的系统表和视图的基础上，我们很难在没有预先准备的基础上解决问题。充满的tempdb有时可以像单个SPID那么简单，有时像一组会话一样复杂，但是上面我所概述的这些步骤帮你将问题化小。

作者

Matthew Schroeder
Matthew Schroeder

Matt在SQL Server和Oracle这两个领域具有12年的经验。他获得微软MCITP认证、是一名数据库开发人员，他还获得了计算机科学专业硕士学位是SQL Server数据库系统高级软件工程师，范围从2 GB到3+ TB、2k和40+ktrans/sec之间。目前他任职于IGT公司，同样是一名独立的咨询师、专攻覆盖自动化、电子商务、娱乐和银行业的SQL Server、Oracle以及.NET方面。Matt擅长OLTP/OLAP数据库管理系统以及用.NET语言写可升级的处理系统。

翻译

April
April

相关推荐