日志暴大，无法收缩，谁来挑战一下！

大力水手 2013-12-20 06:17:23



环境：windows server 2008  sqlserver 2008

报磁盘空间满，上去查看发现数据库的ldf文件暴大，本人随已不用sqlserver 1年，但是以前干过，对sqlserver谈不上精通，也大概知道点皮毛？

立即写脚本操作，步骤如下。

alter database DBName set recovery simple;      --逻辑操作，VLF 248 kb 标记为可重用，磁盘空间不会缩小

use DBName 

DECLARE @lname AS VARCHAR(50)   

SELECT name

FROM sys.database_files WHERE type=1

DBCC SHRINKFILE (@lname,100);                   --物理操作收缩 截断了的空间。但是空间一点没有变小。所以怀疑有可能是一个很大很长的事务在执行。

alter database DBName set recovery full;



use DBName

go

dbcc opentran

--结果如下

/*

已复制的事务信息:

        最早的分布式 LSN     : (0:0:0)

        最早的非分布式 LSN : (5067131:1370:2)

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

*/



DBCC loginfo()  --全是2 木有 0 都是活动事务，断不了，更收缩不了。



-- 一个没有spid的东西。怎么杀啊？ 查事务，

select transaction_begin_time,  

case transaction_type   

when 1 then 'Read/Write transaction'  

when 2 then 'Read-Only transaction'  

when 3 then 'System transaction'  

when 4 then 'Distributed transaction'  

end tran_Type,  

case transaction_state  

when 0 then 'not been comoletely initaialiaed yet'  

when 1 then 'initaialiaed but ha notstarted'  

when 2 then 'active'  

when 3 then 'ended (read-only transaction)'  

when 4 then 'commit initiated for distributed transaction'  

when 5 then 'transaction prepared and waiting resolution'  

when 6 then 'commited'  

when 7 then 'being rolled back'  

when 0 then 'been rolled back'  

end transaction_state  

from   

sys.dm_tran_active_transactions

--没有发现异常的事务。没有做过复制，没有做过镜像。



--查看log状态

SELECT log_reuse_wait_desc  FROM sys.databases WHERE NAME='DBName' 

--REPLICATION  某做过复制，竟然出来个这。



use DBName

checkpoint

go

sp_removedbreplication 'DBName'

DBCC SHRINKFILE(DBName_Log,100);



DBCC loginfo()  --还是全是活动的。

dbcc opentran 还有的那个没有spid的复制事务。



某有头绪了！

临时解决方案，新建了个log文件在其他盘，能顶个10几天。趁这段时间跑来问问大牛？

...全文

1147 45 打赏收藏转发到动态举报

写回复

用AI写文章

45 条回复

切换为时间正序

请发表友善的回复…

发表回复

LongRui888 2014-01-04

打赏
举报

引用 44 楼 public0011 的回复:

解决了，谢谢各位，什么招都用了，以前DBA改过计算机名称。改回来做复制再把复制删了，不然sp_removedbrepliation根本没用，删除复制后做个log备份（一档log 1TB）。备份完这一档log空间就可以收缩了！谢谢！

原来是这样啊，呵呵，我得记录下来，宝贵的经验。谢谢分享

大力水手 2014-01-04

打赏
举报

解决了，谢谢各位，什么招都用了，以前DBA改过计算机名称。改回来做复制再把复制删了，不然sp_removedbrepliation根本没用，删除复制后做个log备份（一档log 1TB）。备份完这一档log空间就可以收缩了！谢谢！

专注or全面 2013-12-24

打赏
举报

我遇到过收缩日志无效的情况，重启sqlserver服务后，设置简单恢复模式，收缩成功

KevinLiu 2013-12-24

打赏
举报

引用 41 楼 public0011 的回复:

[quote=引用 40 楼 DBA_Huangzj 的回复:] [quote=引用 39 楼 public0011 的回复:] [quote=引用 38 楼 SmithLiu328 的回复:] [quote=引用 35 楼 public0011 的回复:] [quote=引用 34 楼 SmithLiu328 的回复:] [quote=引用 32 楼 public0011 的回复:] 上面的所有的方法都试过了，好像不行，还有没有其他思路！

确定没有运行的事物？有没有分布式的？[/quote] 就有一个复制的事务一直在跑 /* 已复制的事务信息: 最早的分布式 LSN : (0:0:0) 最早的非分布式 LSN : (5067131:1370:2) DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 */ 而且怎么都杀不掉，也remove不掉着repliaction [/quote] 把复制的Agent都停掉然后试试看。[/quote] 我从来没有创建过复制，也没有复制的job[/quote]37楼的呢？[/quote] 这些前几天都查过了,没有什么非法的事务. 都是基本的select insert 语句.所以我才跑来问的. 各种方法都用了. 单用户收缩, 重启sqlserver的服务! 查了不少资料...楞是弄不下去![/quote] SQL Server错误日志里面可以看到什么吗？查一下fn_dblog看一下到底是什么交易啊。

大力水手 2013-12-24

打赏
举报

引用 40 楼 DBA_Huangzj 的回复:

[quote=引用 39 楼 public0011 的回复:] [quote=引用 38 楼 SmithLiu328 的回复:] [quote=引用 35 楼 public0011 的回复:] [quote=引用 34 楼 SmithLiu328 的回复:] [quote=引用 32 楼 public0011 的回复:] 上面的所有的方法都试过了，好像不行，还有没有其他思路！

發糞塗牆 2013-12-24

打赏
举报

引用 39 楼 public0011 的回复:

[quote=引用 38 楼 SmithLiu328 的回复:] [quote=引用 35 楼 public0011 的回复:] [quote=引用 34 楼 SmithLiu328 的回复:] [quote=引用 32 楼 public0011 的回复:] 上面的所有的方法都试过了，好像不行，还有没有其他思路！

大力水手 2013-12-24

打赏
举报

引用 38 楼 SmithLiu328 的回复:

[quote=引用 35 楼 public0011 的回复:] [quote=引用 34 楼 SmithLiu328 的回复:] [quote=引用 32 楼 public0011 的回复:] 上面的所有的方法都试过了，好像不行，还有没有其他思路！

KevinLiu 2013-12-24

打赏
举报

引用 35 楼 public0011 的回复:

[quote=引用 34 楼 SmithLiu328 的回复:] [quote=引用 32 楼 public0011 的回复:] 上面的所有的方法都试过了，好像不行，还有没有其他思路！

發糞塗牆 2013-12-24

打赏
举报

或者你用这些插叙来看看 select * from sys.dm_tran_active_transactions select * from sys.dm_tran_session_transactions

發糞塗牆 2013-12-24

打赏
举报

你是用DBCC OPENTRAN来找的？没有对应的SPID？我随便测一下是有的哦： Transaction information for database 'tempdb'. Oldest active transaction: SPID (server process ID): 57 UID (user ID) : -1 Name : user_transaction LSN : (53:16:269) Start time : Dec 24 2013 2:15:04:867PM SID : 0x010500000000000515000000483868ecae0297feb98ac715e9030000 DBCC execution completed. If DBCC printed error messages, contact your system administrator.

大力水手 2013-12-24

打赏
举报

引用 34 楼 SmithLiu328 的回复:

[quote=引用 32 楼 public0011 的回复:] 上面的所有的方法都试过了，好像不行，还有没有其他思路！

KevinLiu 2013-12-23

打赏
举报

引用 32 楼 public0011 的回复:

上面的所有的方法都试过了，好像不行，还有没有其他思路！

确定没有运行的事物？有没有分布式的？

山寨DBA 2013-12-23

打赏
举报

试试下面这个，先手动执行一下checkpoint，再清下缓冲区，然后再收缩：


GO checkpoint; 
dbcc dropcleanbuffers;

GO  
dump transaction @库名 with no_log ; 
backup log @库名 with no_log ; 
dbcc shrinkdatabase(@库名)  ; 
dbcc shrinkfile(@file_id)

---select * from sysfiles ---找到@file_id就是那个log文件的id

大力水手 2013-12-23

打赏
举报

上面的所有的方法都试过了，好像不行，还有没有其他思路！

haitao 2013-12-21

打赏
举报

mssql的日志，异常起来的确头痛好像没什么明确的方法——明确的收缩命令，此时已经无效了。。。。

KevinLiu 2013-12-21

打赏
举报

引用 23 楼 public0011 的回复:

[quote=引用 21 楼 DBA_Huangzj 的回复:] 好像发现问题了： EXEC sp_removedbreplication msdb 试一下remove msdb的

我还有一个数据库的状态一直是checkpoint。master和tempdb一直是ACTIVE_TRANSACTION 对问题排除有帮助吗？ Monitor CHECKPOINT master ACTIVE_TRANSACTION tempdb ACTIVE_TRANSACTION[/quote] 有活动的Transcation看看是什么再跑啊是否可以Kill掉？不过KILL也要ROLLBACK 需要空间。