ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 在SQL Server tempdb满时检查数据文件

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

原创 Linux操作系统 作者:iSQlServer 时间:2009-05-12 14:52:28 0 删除 编辑
作为一名数据库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那么简单,有时像一组会话一样复杂,但是上面我所概述的这些步骤帮你将问题化小。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-594831/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    1319
  • 访问量
    2085017