SqlServer数据库提示 “tempdb” 的日志已满 问题解决方案

日期:2020-05-07编辑作者:数据库

执行sql 语句,中间没有用到临时表

1.输入结果等于输出结果
2.类似Group By的聚合
3.非顺序的访问数据
4.可以对于窗口函数使用分析函数 聚合函数和排名函数
5.简化SQL代码 消除Join
6.消除中间表

提示服务器: 消息 9002,级别 17,状态 2,行 1数据库 'tempdb' 的日志已满。请备份该数据库的事务日志以释放一些日志空间。

窗口函数是整个SQL语句中最后被执行的部分,窗口函数实在SQL查询的结果集上进行的,不会受到Group By,Hiving Where 字句的影响。

网上找了下解决方案,大体是扩大临时库的日志文件的大小解决的

函数() Over (Partition By 列1,列2, order By 列3, 窗口字句) As 列别名
select [LoginId],gender,(select Count() from employee a where a.gender = b.gender) as genderTotal from employee b
select [LoginID],gender, Count(
) Over (PARTITION BY gender) AS genderTotal from employee
select loginId,first_value(loginId) OVER(PARTITION BY Organizationlevel
order by sickleaveHours desc rows between 1 preceding and 1 follwing) from employee

解决过程:查看了下数据库的属性,是自动增长,不指定文件大小上限。在网上Google了很久,试了些方法都不行;数据库所在磁盘还有很大的可用空间,试着下重药了。直接把tempdb的数据文件和日志文件的大小改为3000M,问题解决。

first_value(loginid) over (partition by organizationLevel order by sickleaveHours desc Range between unbounded preceding and current now) from employee

记录一下清空日志的命令:

大表文件操作:
我们的表里都是只存放一天的数据。其中三张表数据较大。最大的一张表单表在5亿行左右,随时间处于波动增长状态。目前碰到最多的一天单表有7亿多行。另外两张表数据量在0.5亿到1亿之间。其余的就是一些几百万行~一千万行记录的表。需要进行十几套不同条件维度的聚合计算,且这十几套计算都会用到那三张大表。查询里会涉及到多张大表连接操作。
我们目前的处理方式就是尽量精简存储的数据量。各表数据存储都不存在冗余的状况,但是会导致分成多个表;数据类型尽量设置成存储长度较小的类型;对常用的连接、查询列建立了合适的索引;对数据和索引都设置了页压缩模式减少存储量。
在我们目前其他的查询处理中,只要应用了合适的索引+页压缩+适当的分表(历史数据归档)模式,可以解决绝大多数的性能问题,不仅可以提升查询性能也能提升插入性能。此处也有单表接近1亿行的数据查询处理,效果也很好。只是这里不存在几张大表连接的情况。

DUMP TRANSACTION dbname WITH NO_LOG

数据库日志满了的解决方案:
提示:对于该问题,主要检是否限制了数据库文件增长
1.检查你的磁盘剩余空间是否足够,如果没有磁盘剩余空间,则清理磁盘,腾出空间
2.检查你的磁盘分区格式
如果是FAT16,则数据文件最大只能是2G;如果是FAT32,则数据文件最大只能是4G,改为NTFS分区则没有这种限制
3.检查一下你有没有限制数据库文件的大小
企业管理器--右键你的数据库--属性--文件增长限制--如果有限制大小,取消限制
4.检查你的SQL版本,如果你用MSDE,则限制了数据文件最大是2G
5.你也可以为 primary 组添加新的数据文件来解决这个问题
alter database 库名 add file(NAME = 逻辑文件名,FILENAME = c:实际文件名.ndf
另外,请注意TEMPDB的空间占用情况!该临时数据库应该经常清理!

截断事务日志命令:

清理日志的方法

BACKUP LOG dbname WITH NO_LOG

当SQL数据库日志文件已满,可以取消日志文件大小限制,当然我们更希望定期对其压缩,清除N天前的无用记录
提供一种压缩日志及数据库文件的方法如下:
1.清空日志
DUMP TRANSACTION 库名 WITH NO_LOG
2.截断事务日志:
BACKUP LOG 数据库名 WITH NO_LOG
3.收缩数据库文件(如果不压缩,数据库的文件不会减小
企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件
--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
--选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
也可以用SQL语句来完成
--收缩数据库
DBCC SHRINKDATABASE(客户资料)
--收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfiles
DBCC SHRINKFILE(1)
4.为了最大化的缩小日志文件(如果是sql 7.0,这步只能在查询分析器中进行)
a.分离数据库:
企业管理器--服务器--数据库--右键--分离数据库
b.在我的电脑中删除LOG文件
c.附加数据库:
企业管理器--服务器--数据库--右键--附加数据库
此法将生成新的LOG,大小只有500多K
或用代码:
下面的示例分离 pubs,然后将 pubs 中的一个文件附加到当前服务器。
a.分离
E X E C sp_detach_db @dbname = pubs
  b.删除日志文件
c.再附加
E X E C sp_attach_single_file_db @dbname = pubs,
@physname = c:Program FilesMicrosoft SQL ServerMSSQLDatapubs.mdf
5.为了以后能自动收缩,做如下设置:
企业管理器--服务器--右键数据库--属性--选项--选择"自动收缩"
--SQL语句设置方式:
E X E C sp_dboption 数据库名, autoshrink, TRUE
6.如果想以后不让它日志增长得太大
企业管理器--服务器--右键数据库--属性--事务日志
--将文件增长限制为xM(x是你允许的最大数据文件大小)
--SQL语句的设置方式:
alter database 数据库名 modify file(name=逻辑文件名,maxsize=20)
特别注意:
请按步骤进行,未进行前面的步骤,请不要做后面的步骤
否则可能损坏你的数据库.
www.129028.com金沙,一般不建议做第4,6两步
第4步不安全,有可能损坏数据库或丢失数据
第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复.

本文由www.129028.com金沙发布于数据库,转载请注明出处:SqlServer数据库提示 “tempdb” 的日志已满 问题解决方案

关键词:

Sql Server中的非聚集索引详细介

这篇我们来解开神秘面纱。 3:通过最后的槽位列表,可以得知173号索引页上存放着8条索引记录。 复制代码 代码如下...

详细>>

必须会的SQL语句(一) 创建数据库与删除数据库【www.129028.com金沙

1.创建数据库 drop命令用于删除数据库。 Create database 名称on primary{ name ='名称', filename ='c:xx名称.mdf', size = 10mb, --数据库...

详细>>

SqlServer提示“列前缀tempdb.无效: 未指定表名”问题解决方案www

在查询凭证、审核凭证时出现“列前缀tempdb.无效:未指定表名”的错误提示,怎么解决? 本文我们总结了几个在安装...

详细>>

【www.129028.com金沙】SQL Server中将查询结果转换为Json格式脚本分享

脚本源码: 复制代码 代码如下:SET ANSI_NULLS ONGOSETQUOTED_IDENTIFIER ONGOCREATEPROCEDURE[dbo].[SerializeJSON](@ParameterSQL ASVARCHAR(MAX)...

详细>>