日志暴大,无法收缩,谁来挑战一下!

大力水手 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几天。趁这段时间跑来问问大牛?
...全文
1146 45 打赏 收藏 转发到动态 举报
AI 作业
写回复
用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 的回复:] 上面的所有的方法都试过了,好像不行,还有没有其他思路!
确定没有运行的事物?有没有分布式的?[/quote] 就有一个复制的事务一直在跑 /* 已复制的事务信息: 最早的分布式 LSN : (0:0:0) 最早的非分布式 LSN : (5067131:1370:2) DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。 */ 而且怎么都杀不掉,也remove不掉着repliaction [/quote] 把复制的Agent都停掉然后试试看。[/quote] 我从来没有创建过复制,也没有复制的job[/quote]37楼的呢?[/quote] 这些前几天都查过了,没有什么非法的事务. 都是基本的select insert 语句.所以我才跑来问的. 各种方法都用了. 单用户收缩, 重启sqlserver的服务! 查了不少资料...楞是弄不下去!
發糞塗牆 2013-12-24
  • 打赏
  • 举报
回复
引用 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楼的呢?
大力水手 2013-12-24
  • 打赏
  • 举报
回复
引用 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
KevinLiu 2013-12-24
  • 打赏
  • 举报
回复
引用 35 楼 public0011 的回复:
[quote=引用 34 楼 SmithLiu328 的回复:] [quote=引用 32 楼 public0011 的回复:] 上面的所有的方法都试过了,好像不行,还有没有其他思路!
确定没有运行的事物?有没有分布式的?[/quote] 就有一个复制的事务一直在跑 /* 已复制的事务信息: 最早的分布式 LSN : (0:0:0) 最早的非分布式 LSN : (5067131:1370:2) DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。 */ 而且怎么都杀不掉,也remove不掉着repliaction [/quote] 把复制的Agent都停掉然后试试看。
發糞塗牆 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 的回复:] 上面的所有的方法都试过了,好像不行,还有没有其他思路!
确定没有运行的事物?有没有分布式的?[/quote] 就有一个复制的事务一直在跑 /* 已复制的事务信息: 最早的分布式 LSN : (0:0:0) 最早的非分布式 LSN : (5067131:1370:2) DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。 */ 而且怎么都杀不掉,也remove不掉着repliaction
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 需要空间。
line_us 2013-12-21
  • 打赏
  • 举报
回复
看看究竟是怎么回事。
莫流离 2013-12-20
  • 打赏
  • 举报
回复
看楼上的牛人 啊
LongRui888 2013-12-20
  • 打赏
  • 举报
回复
引用 26 楼 public0011 的回复:
[quote=引用 25 楼 yupeigu 的回复:] 能不能考虑,在维护期间,把数据库备份一下,然后再还原回来,会不会解决这个问题
呵呵,备份+还原估计要一个上午的时间。 而且还原回来log应该还是那么大。状态应该没有什么改变![/quote] 备份,然后还原在其他的机器上呗,试试,看能不能解决。
大力水手 2013-12-20
  • 打赏
  • 举报
回复
引用 25 楼 yupeigu 的回复:
能不能考虑,在维护期间,把数据库备份一下,然后再还原回来,会不会解决这个问题
呵呵,备份+还原估计要一个上午的时间。 而且还原回来log应该还是那么大。状态应该没有什么改变!
加载更多回复(25)

22,301

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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