none
为什么tempdb所占用的磁盘空间瞬间暴涨,持续一段时间之后,又将磁盘空间释放? RRS feed

  • 问题

  • 在监控磁盘剩余空间的时候,发现tempdb所在的磁盘在半夜有过在某一时间点瞬间增长60G的占用空间,持续一个小时后,又将这60G的空间释放出来。

    这个是什么情况?

    2013年11月13日 7:31

答案

  • LZ您好,您说的事情应该不可能吧,如果有日志记录的话,LZ可以把日志记录贴上来的

    在使用sql server时您可能遇到过下面的情况,tempdb的数据文件初始大小为3mb, 随着对tempdb的使用,tempdb文件逐渐变得很大(例如30GB),导致了磁盘空间不足。 此时您需要立刻释放tempdb文件所占用的空间,这时你会有两个选择:

    重启SQL Server。大家都知道SQL Server重启时会创建一个新的tempdb。由于tempdb的initial size并不会随着文件大小的增长而增长,重启SQL Server就会创建一个新的3MB的tempdb。
    使用DBCC命令收缩tempdb的大小。如果当前tempdb实际被使用到的空间其实不大,只是文件尺寸很大的话,您可能会考虑使用dbcc shrinkfile来收缩tempdb。通常情况下,这种方案是可行。但是您需要警惕一个潜在的风险。下面是我最近遇到的一个实际的案例。

    http://www.cnblogs.com/stswordman/p/3358496.html

    2013年11月13日 10:59

全部回复

  • LZ您好,您说的事情应该不可能吧,如果有日志记录的话,LZ可以把日志记录贴上来的

    在使用sql server时您可能遇到过下面的情况,tempdb的数据文件初始大小为3mb, 随着对tempdb的使用,tempdb文件逐渐变得很大(例如30GB),导致了磁盘空间不足。 此时您需要立刻释放tempdb文件所占用的空间,这时你会有两个选择:

    重启SQL Server。大家都知道SQL Server重启时会创建一个新的tempdb。由于tempdb的initial size并不会随着文件大小的增长而增长,重启SQL Server就会创建一个新的3MB的tempdb。
    使用DBCC命令收缩tempdb的大小。如果当前tempdb实际被使用到的空间其实不大,只是文件尺寸很大的话,您可能会考虑使用dbcc shrinkfile来收缩tempdb。通常情况下,这种方案是可行。但是您需要警惕一个潜在的风险。下面是我最近遇到的一个实际的案例。

    http://www.cnblogs.com/stswordman/p/3358496.html

    2013年11月13日 10:59
  • 应该是定时任务产生了大量临时磁盘空间占用,且已知会占TEMPDB,所以任务执行完后又收缩它

    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2013年11月14日 1:19
  • 你的数据库中一定使用了大量的临时表
    2013年11月25日 9:27
  • Sql doesn't shrink tempdb automatically, will recreate it every time starting sql.
    2013年11月25日 14:26
  • 这标记的答案是嘛呀?
    2013年12月2日 9:32
  • 这个问题目前还没有得到解决,上一次tempdb暴涨是在11月12日,重启sql 后一切恢复正常,而前几天tempdb又暴涨,最后涨了60G,收缩temp数据库文件和日志,tempdb并没有减小,重启sql后才变小。

    sql版本是sql2000,有没有办法监控是什么原因导致的tempdb暴涨么?只能重启sql server才能解决这个问题么?

    谢谢!

    2014年1月8日 6:18
  • 在2000里面检测还是比较弱的,是否可以通过检测一些事件比如DB File Growth,发现有tempdb 文件增长的时候自动发邮件给你,然后你查一下看看当时有什么语句运行,是否有这些语句导致的。


    Please Mark As Answer if it is helpful.

    2014年1月8日 7:00
  • tempdb暴涨一两个月才发生一次,看来只能监控文件大小,然后 用sql profile来监控当时运行的sql了。

    希望哪位大神还有其他办法。。

    2014年1月8日 9:34
  • 我觉得可以结合

    KevinLiu328大侠和下面这篇文章的思路
    如何有效抓取SQL Server的BLOCKING信息
    --http://blogs.msdn.com/b/apgcdsd/archive/2011/12/12/sql-server-blocking.aspx

    方法四,我们用Agent Job来检查BLOCKING
    =====================================
    长期运行一个BLOCKING SCRIPT的缺点是我们每隔一段时间,去查询信息,但是大多数收集的信息是无用的。所以会导致日志文件巨大,对于一个生产系统来讲,磁盘空间满可不是个好事情,另外,有一些客户对于用命令行来长期运行TSQL脚本有所顾忌,所以我们做了一个改进。这次,我们只收集有用的信息。对于无用的信息我们不关注。这样能极大减少日志大小。

    2014年1月8日 13:56
  • Blocking causes tempdb growth? Set profiler to trace tempdb processes sounds more reasonable. 
    2014年1月8日 14:09
  • Blocking causes tempdb growth? Set profiler to trace tempdb processes sounds more reasonable. 

    Blocking 当然不会引起 tempdb growth啦

    我的意思是根据文章中说的,创建一些表和job,定期保存当时tempdb的growth的信息

    2014年1月8日 14:14
  • 刚发现tempdb的大小就在这几个小时就突然变大了,

    凌晨记录的如下大小为

    tempdb.mdf 0.9765625 G

    templog.ldf size: 0.118225098 G

    现在是

    tempdb.mdf 0.9765625 G

    templog.ldf size: 5.893432617G

    为什么日志会突然暴涨呢?在压缩前我想弄清原因。谢谢!



    2014年1月9日 3:04
  • 在这台数据库上,我用profile跟踪,发现几乎所有的操作都是由创建临时存储过程来做select操作的,然后再传参数给这个临时存储过程。

    对于update操作,也是新建临时存储过程,然后再传参数来更新。

    用完临时存储过程之后,就drop掉。

    就想下面的这个查询操作一样,先创建一个临时存储过程,然后传参,然后drop掉。

    create proc #jtds020CDA @P0 varchar(8000) as select order_.detailid as deid,.........


    2014年1月9日 3:33
  • 设计的问题了,能改吗?


    Please Mark As Answer if it is helpful.

    2014年1月9日 3:56
  • templog.ldf size: 5.893432617G

    tempdb的log文件这么大是不是开了显式事务?而没有关闭

    导致日志暴涨,你用dbcc opentran看一下呢

    2014年1月9日 4:03
  • templog.ldf size: 5.893432617G

    tempdb的log文件这么大是不是开了显式事务?而没有关闭

    导致日志暴涨,你用dbcc opentran看一下呢

    dbcc opentran

    Transaction information for database 'tempdb'.

    Oldest active transaction:
        SPID (server process ID) : 72
        UID (user ID) : -1
        Name          : implicit_transaction
        LSN           : (61486:46546:8)
        Start time    : Jan  9 2014 12:07:13:090PM
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    运行后出现上面的结果,这个是什么情况?

    2014年1月9日 4:08
  • 设计的问题了,能改吗?


    Please Mark As Answer if it is helpful.

    这个不好改。。都用了好多年了。。
    2014年1月9日 4:08
  • Run 'sp_who2 72' to find out detail of that process, and you can get statement with 'dbcc inputbuffer (72)'.
    2014年1月9日 4:32
  • 我看了一下 dbcc opentran

    这个结果,这个事务的开始时间就是刚刚,然后过一会这个事务就没啦,就显示下面的:

    No active open transactions.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    ----------------

    运行

    sp_who2 72
    dbcc inputbuffer (72)

    之后,结果如下:

    SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID
    72   sleeping                    cite SHV410WIBLUES03 .   KMM AWAITING COMMAND 4617392 166532 01/09 12:40:25 kmm6901 72   

    EventType Parameters   EventInfo

    Language Event 0  SET IMPLICIT_TRANSACTIONS OFF



    • 已编辑 啵啵猪 2014年1月9日 4:43 格式不对
    2014年1月9日 4:42
  • 你现在执行是没有用的,你需要监控dbcc opentran的输出,每个一段时间就输出事务信息

    自己写job,每隔一段时间执行那个job,把dbcc open tran和sp_who2的输出信息记录到表中

    到晚上半夜的时候,就可以捕获到案发的时候的信息了


    2014年1月9日 5:18
  • 你现在执行是没有用的,你需要监控dbcc opentran的输出,每个一段时间就输出事务信息

    自己写job,每隔一段时间执行那个job,把dbcc open tran和sp_who2的输出信息记录到表中

    到晚上半夜的时候,就可以捕获到案发的时候的信息了


    那我现在就写job。

    tempdb中开显示事务,一直不关闭的话,tempdb就会一直暴涨么?

    今天从早上8点多到现在,tempdb的log一直在变大,最新的大小如下,这都是白天变大的,怎么解释?

    tempdb.mdf 0.9765625 G

    templog.ldf size: 11.48480225 G


    2014年1月9日 5:50
  • tempdb中开显示事务,一直不关闭的话,tempdb就会一直暴涨么

    是啊,因为tempdb是简单恢复模式,事务提交肯定会截断的,但是如果没有提交,日志就会不断暴涨

    2014年1月9日 9:18
  • tempdb中开显示事务,一直不关闭的话,tempdb就会一直暴涨么

    是啊,因为tempdb是简单恢复模式,事务提交肯定会截断的,但是如果没有提交,日志就会不断暴涨

    现在tempdb的日志大小已经到18 G了,压缩tempdb的日志并没有变小多少。目前监控的job已经写好在运行了,到目前为止还没有发现时间太长的事务。

    有个问题,如果我执行以下语句,日志会不会变小呢?

    BACKUP LOG tempdb WITH NO_LOG

    2014年1月9日 9:26
  • 压缩日志是没有用的,因为tempdb是简单模式,你上面的语句日志也不会变小

    如果不明白可以看一下这篇文章

    您真的理解了SQLSERVER的日志链了吗?

    use [tempdb]
    GO
    SELECT * FROM [sys].[fn_dblog](NULL,NULL)
    上面的语句可以看到日志里究竟最多的是哪些语句,LZ你可以分析一下
    2014年1月9日 11:15
  • Find out who started spid 72 and ask owner what the process did, ensure app cleans up temp objects properly.
    2014年1月9日 14:13
  • 压缩日志是没有用的,因为tempdb是简单模式,你上面的语句日志也不会变小

    如果不明白可以看一下这篇文章

    您真的理解了SQLSERVER的日志链了吗?

    use [tempdb]
    GO
    SELECT * FROM [sys].[fn_dblog](NULL,NULL)
    上面的语句可以看到日志里究竟最多的是哪些语句,LZ你可以分析一下
    我这个是sql 2000,没有 sys.fn_dblog这个的。
    2014年1月10日 3:11