如何防止TempDB log文件嗷嗷增长

freekuangmo 2007-11-21 02:37:31
我们有一台服务器,最近经常收到报警,The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
我察看了tempdb得属性,simple得,为什么log会增长的那么快呢?
我压缩了log文件,但是第二天又增长了很大,磁盘空间不足。

请教。。。
...全文
1767 27 打赏 收藏 转发到动态 举报
写回复
用AI写文章
27 条回复
切换为时间正序
请发表友善的回复…
发表回复
Garnett_KG 2008-08-06
  • 打赏
  • 举报
回复



CHECKPOINT
GO

BACKUP LOG TEMPDB WITH NO_LOG
GO

--这样应该会截斷已经提交了的日志

--然后再SHRINKFILE应该会缩小一些日志

Garnett_KG 2008-08-06
  • 打赏
  • 举报
回复
[Quote=引用 23 楼 bwu851 的回复:]
看来看去, 就是12楼, 17~18楼说得对路子.

首先, LZ的服务器是不可能重启的. 在收到警告时, 一般都是公司商务活动最忙的时候, 重启会使LZ公司的数据丢失或事务终止, 估计LZ会被公司开了.
再次, 根据LZ的情况, tempdb是不可能设置限制增长的. 否则, 结果同上.

所以, LZ需要分析为什么tempdb增长太快. 怎么预防那个警告.

大多数人人认为tempdb是用于临时表的. 其实, 你根本不需要建临时表就可以很容易的使tempdb增大. 比如…
[/Quote]

LZ是说Log日志档在增长啦...不是数据档.

你说的1234567是会在tempdb暂存数据,但是日志不会记录到Tempdb中去.

我还是怀疑是你的系统里面大量使用TEMP TABLE造成的。

 

DBCC LOG (tempdb,2)

--查[Object Name]一栏就知道是什么原因造成的了。


bwu851 2008-08-06
  • 打赏
  • 举报
回复
短期的补救方式:
1. shrink tempdb -- 不多说了.
2. 用DBCC OPENTRAN看看有没有长时间运行的事务(一般是因为没commit), 然后可以的话, 用KILL把该session终止.
3. 重启 --- 这个对LZ应该不适用.

长期的方法:
(见18楼)
***************************

根据LZ所说的现象, 偶认为是
1) tempdb没在单独的硬盘上, 或者是硬盘空间太小.
2) 既然经常增大, 说明LZ公司的商务活动需要很大的tempdb, 也就说明LZ设置的tempdb的初始空间太小.(如果经过分析后, 没什么别的异常, 比如, 没有长时间运行的事务等, 那更说明该tempdb应该设置较大的空间)

bwu851 2008-08-06
  • 打赏
  • 举报
回复
下面的语句告诉你tempdb的分配空间:

use tempdb
go
exec sp_spaceused
go

下面的代码告诉你那些表占用了tempdb的很多空间 -- 这可以使你缩小查询范围: 看那些事务运行了很长时间或经常被留在tempdb里:

USE tempdb
GO
SELECT name
FROM tempdb..sysobjects

SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC

较大的rowcount值说明比较大的临时表占用了tempdb的空间. 当然, 因为tempdb用于内部I/O和其他排序过程, 所以可能你看不出什么来. 不过, 你还是可以缩小调查范围的.(比如, 你可以再通过查询INFORMATION__SCHEMA.ROUTINES..WHERE ROUTINE_DEFINITION LIKE '%#table_name%' 来查询哪个存储过程占用了很多tempdb空间)
另外, 你可以用profiler来看数据库文件和日志文件的auto grow事件, 如果有, 说明你设置的tempdb空间不够大.
bwu851 2008-08-06
  • 打赏
  • 举报
回复
看来看去, 就是12楼, 17~18楼说得对路子.

首先, LZ的服务器是不可能重启的. 在收到警告时, 一般都是公司商务活动最忙的时候, 重启会使LZ公司的数据丢失或事务终止, 估计LZ会被公司开了.
再次, 根据LZ的情况, tempdb是不可能设置限制增长的. 否则, 结果同上.

所以, LZ需要分析为什么tempdb增长太快. 怎么预防那个警告.

大多数人人认为tempdb是用于临时表的. 其实, 你根本不需要建临时表就可以很容易的使tempdb增大. 比如:
1. 任何排序查询使用的memory超过了SQL SERVER所被分配的memory, 该查询就会被强制在tempdb里进行.
2. 任何排序所要求的空间超过了tempdb被分配的空间
3. DBCC CHECKDB是使用tempdb的空间进行的. 当遇到较大的表是, 会"吃掉"很多tempdb的空间.
4. DBCC DBREINDEX或者其他DBCC..with 'Sort in tempdb'选项的也会占用很多tempdb的空间.
5. unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table
variables, 和 hashing等所产生的大的查询结果也很占tempdb的空间.
6. 任何没commit和roll back的事务会留在tempdb里
7. 使用ODBC DSN加上'建临时存储过程'选项的, 会在连接其间把创建的object留在tempdb里.

comszsoft 2008-08-04
  • 打赏
  • 举报
回复
gz
S314324153 2008-08-04
  • 打赏
  • 举报
回复
关注,俺也碰到这个问题
tcxx2008 2007-11-26
  • 打赏
  • 举报
回复
关注中
happydreamer 2007-11-26
  • 打赏
  • 举报
回复

以下查询可以看到tempdb的变化
select a.filename,a.name,a.size*8.0/1024.0 as originalsize_MB,
f.size*8.0/1024.0 as currentsize_MB
from master..sysaltfiles a join tempdb..sysfiles f on a.fileid=f.fileid
where dbid=db_id('tempdb')
and a.size<>f.size
中国风 2007-11-25
  • 打赏
  • 举报
回复

tempdb这个临时数据库,它对性能的影响较大。tempdb和其他数据库一样可以增大,可以缩小。当数据文件需要增长的时候,通常不能保持剩余部分的连续性。这时文件就会产生碎片,这种碎片会造成性能下降。这种碎片属于外来性碎片。要阻止在tempdb中产生外来性碎片,必须保证有足够的硬盘空间。一般将tempdb的容量放到平均使用容量。而你也应该允许tempdb自动增长,比如你有个一个超大的join操作,它建立了一个超过tempdb容量的时候,该查询将失败。你还要设置一个合理的单位增长量。因为如果你设得太小,将会产生许多外来性碎片,反而会占用更多资源。sqlserver调优最有效的做法之一,就是把争夺资源的操作独立出去。tempdb就是一个需要独立出去的部分而tempdb和其他系统库一样是公用的,是存取最可能频繁的库,所有处理临时表、子查询、GROUP BY、排序、DISTINCT、连接等等。它最适合放到一个具有快速读写能力的设备上


中国风 2007-11-25
  • 打赏
  • 举报
回复
限制tempdb库大小反而降低数据库的性能:
用Profiler来跟踪查询,得到查询所需的时间,找出SQL的问题所在;用索引优化器优化索引


c)tempdb的使用规范:

   i. 尽量避免使用distinct、order by、group by、having、join、cumpute,因为这些语句会加重tempdb的负担。

   ii. 避免频繁创建和删除临时表,减少系统表资源的消耗。

   iii. 在新建临时表时,如果一次性插入数据量很大,那么可以使用select into代替create table,避免log,提高速度;如果数据量不大,为了缓和系统表的资源,建议先create table,然后insert。

   iv. 如果临时表的数据量较大,需要建立索引,那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中,这样才能保证系统能够很好的使用到该临时表的索引。

    v. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先truncate table,然后drop table,这样可以避免系统表的较长时间锁定。

    vi. 慎用大的临时表与其他大表的连接查询和修改,减低系统表负担,因为这种操作会在一条语句中多次使用tempdb的系统表。  
-狙击手- 2007-11-25
  • 打赏
  • 举报
回复
对tempdb库进行一下这个设置看看:

企业管理器->服务器->数据库->属性->事务日志->将文件增长限制为100M
lyhyp 2007-11-25
  • 打赏
  • 举报
回复
重启服务器或重启SQL服务
云中客 2007-11-22
  • 打赏
  • 举报
回复
解决的方法就是:
1.减少临时表或临时存储过程的使用
2.在有可能性的情况下,对tempdb数据库进行收缩整理
云中客 2007-11-22
  • 打赏
  • 举报
回复
在 tempdb 数据库不发生任何活动时运行 DBCC SHRINKFILE 命令。要确保在执行 DBCC SHRINKFILE 时其他进程不能使用 tempdb,必须以单用户模式重新启动 SQL Server。有关 DBCC SHRINKFILE 的更多信息,请参见本文中在使用 Tempdb 时执行 DBCC SHRINKDATABASE 或 DBCCSHRINKFILE 的结果 一节。
1. 确定主数据文件 (tempdb.mdf)、日志文件 (templog.ldf) 和/或添加到 tempdb 的其他文件的所需大小。确保在这些文件中使用的空间小于或等于所需的目标大小。
2. 用查询分析器连接到 SQL Server,然后为需要收缩的特定数据库运行下列 Transact-SQL 命令:

use tempdb
go

dbcc shrinkfile (tempdev, 'target size in MB')
go
-- this command shrinks the primary data file

dbcc shrinkfile (templog, 'target size in MB')
go
-- this command shrinks the log file, look at the last paragraph.
云中客 2007-11-22
  • 打赏
  • 举报
回复
tempdb 是一个临时工作区。除其他用途外,SQL Server 还将 tempdb 用于:
• 显式创建的临时表的存储。
• 保存在查询处理和排序过程中创建的中间结果的工作表。
• 具体化的静态光标。
SQL Server 在 tempdb 事务日志中记录的信息只足够用于回滚事务,而不足以用于在数据库故障恢复过程中重新执行事务。这一特点提高了 tempdb 中 INSERT 语句的性能。另外,由于每次重新启动 SQL Server 时都会重新创建 tempdb,无需记录用于重新执行任何事务的信息。因此,没有任何要前滚或回滚的事务。当 SQL Server 启动时,通过使用 model 数据库的副本重新创建 tempdb,并将其重置为上次配置的大小。

默认情况下,tempdb 数据库配置为根据需要自动增长;因此,此数据库可能最终增长到大于所需的大小。简单地重新启动 SQL Server 会将 tempdb 的大小重置为上次配置的大小。配置的大小是用文件大小更改操作(如带有 MODIFY FILE 选项的 ALTER DATABASE 或者 DBCC SHRINKFILE 语句)设置的上次显式大小。本文说明您可以用来将 tempdb 收缩到小于其配置的大小的三种方法。
w2jc 2007-11-22
  • 打赏
  • 举报
回复
如果你能重新启动SQL服务器的话,
把tempdb的数据文件和日志文件先转移到另外的硬盘上,再查找原因。
可能是有的应用程序大量使用tempdb做中间存储或什么的。

Moving the tempdb database

You can move tempdb files by using the ALTER DATABASE statement.

1. Determine the logical file names for the tempdb database by using sp_helpfile as follows:

use tempdb
go
sp_helpfile
go

The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.

2. Use the ALTER DATABASE statement, specifying the logical file name as follows:

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go

You should receive the following messages that confirm the change:
Message 1
File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.
Message 2
File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.

3. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.

4. Stop and then restart SQL Server.

晓风残月0110 2007-11-21
  • 打赏
  • 举报
回复
设置自动增长量,
要不先阻断
青锋-SS 2007-11-21
  • 打赏
  • 举报
回复
--俺的客户那儿也经常出现这样的情况,很多数据都丢了
nesec300 2007-11-21
  • 打赏
  • 举报
回复
你现在能不能打开LOG文件,查看具体纪录原因。
很大的可能性是有非法程序的频繁的读入读出数据库
加载更多回复(7)
SQL Server 2005微软官方权威参考手册     是Inside Microsoft SQL Server 2005系列书中的第一本,SQL Server类的顶尖之作   全球公认SQL Server 2005经典著作,囊括大量鲜为人知的技术内幕,大师智慧、专家经验尽览无余。       本系列图书中文版得到了微软总部SQL Server组专家的高度重视,同时也得到了微软中国上海SQL Server全球技术支持中心的高度关注。        本书详细介绍了T-SQL的内部构造,包含了非常全面的编程参考。数据库开发人员和DBA可以通过书中的最佳实践、高级技巧和代码示例来掌握这门复杂的编程语言,以切合实际的方案来解决复杂的问题。本书涵盖了T-SQL程序设计的方方面面,如基于集合的编程技术、日期和时间相关的XML和CLR数据类型的使用、临时对象、T-SQL和CLR用户自定义函数、存储过程、触发器、事务和新的错误处理结构、应用并发模型支持并发用户、使用Service Broker来控制数据库应用程序中的异步处理等。   内容简介 本书是Inside Microsoft SQL Server 2005系列四本著作中的一本。它详细介绍了T-SQL的内部构造,包含了非常全面的编程参考。它提供了使用Transact-SQL(T-SQL)的专家级指导,T-SQL是用于SQL Server的最常见的也是功能最强大的编程语言。该书由Itzik Ben-Gan权威执笔,重点关注语言特性以及它们如何被SQL Server引擎解释和处理。   通过本书,你将深入了解T-SQL的高级用法,包括触发器、用户自定义函数、异常处理等。该书解释并比较了SQL Server 2000和SQL Server 2005在数据库开发相关问题上的解决方案,深入讨论了SQL Server 2005中新增的T-SQL编程特性,包含了大量的代码示例、表示例和逻辑难题以帮助数据库开发人员和管理员理解复杂的逻辑并掌握T-SQL。   本书适合于专业数据库开发者、BI开发者、DBA和以SQL Server作为后台数据库的一般应用程序开发者,读者可以通过书中的最佳实践、高级技巧和代码示例来掌握这门复杂的编程语言,以切合实际的方案来解决复杂的实际问题。 作者简介 Itzik Ben-Gan是Solid Quality Learning的导师和创始人。从1999年开始,他一直是Microsoft SQL Server MVP(最有价值专家),并在全世界已经开展过无数次T-SQL查询、T-SQL优化和编程方面的培训。Itzik是Microsoft SQL Server方面几本著作的作者。他在SQL ServerMagazine和MSDN上发表了许多文章和白皮书。Itzik被邀请在许多会议上做报告,包括TechEd、DevWeek、世界各地的各种SQL用户组、PASS、SQL server Magazine Connections和Solid Quality Learning的会议。 从1992年开始,Itzik就一直致力于涉及各种数据库和计算机系统相关技术的多个课题。除了帮助顾客处理紧迫的要求、修复问题、优化数据库、教学和担任顾问以外,他还帮助开发人员和数据库管理员转变关系/基于集合的理念,改善他们编写代码的性能和可维护性。Itzik擅长T-SQL查询、查询优化、编程和内部原理,此外他还精通其他的数据库领域。1999年,Itzik创立以色列SQL Server和OLAP用户组,一直管理至今。 目录 序 前言 致谢 引言 第1章 数据类型相关的问题,XML和CLR UDT 1.1 DATETIME数据类型 DATETIME的存储格式 时间处理   Datetime相关的查询问题  1.2 与字符相关的问题   模式匹配   区分大小写(Case-Sensitive)的筛选器  1.3 大型对象(Large Object,LOB)   MAX 说明符   BULK行集提供程序  1.4 隐式转换(Implicit Conversion)   标量表达式   筛选表达式  1.5 基于CLR的用户定义类型   UDT理论简介   开发UDT  1.6 XML数据类型   关系数据库中的XML支持   什么时候应该使用XML代替关系表现形式?   数据库中的XML序列化对象   使用开放架构(Open Schema)的XML   作为存储过程参数的XML数据类型   Xquery修改语句  1.7 结论 第2章 临时表和表变量  2.1 临时表   局部临时表   全局临时表  2.2 表变量   限制条件   tempdb   范围和可见性   事务上下文   统计信息  2.3 tempdb相关的注意事项  2.4 表表达式  2.5 比较临时对象  2.6 综合练习——关系分区(Relational Division)  2.7 结论 第3章 游标  3.1 使用游标  3.2 游标开销  3.3 单独处理每一行  3.4 按顺序访问   自定义聚合   连续聚合   最大并发会话   匹配问题  3.5 结论 第4章 动态SQL 第5章 视图 第6章 用户定义函数 第7章 存储过程 第8章 触发器 第9章 事务 第10章 错误处理 第11章 Service Broker 附录A CLR程序指南 A.1 创建CLRUtilities数据库: SQL Server A.2 部署:Visual Studio A.3 部署和测试:Visual Studio 和 SQL Server 索引 中英文术语对照表

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧