我试过了N多方法:
把库设置成简单模式。
declare @dbname varchar(50)
declare temp_cur cursor scroll for select name from sysdatabases
open temp_cur
fetch first from temp_cur into @dbname
while @@fetch_status =0
begin
exec ('backup log '+@dbname+' with no_log')
exec ('dbcc shrinkdatabase('+@dbname+')')
exec ('dbcc checkcatalog ('+@dbname+')')
exec ('dump transaction '+@dbname+' with no_log')
fetch next from temp_cur into @dbname
end
close temp_cur
deallocate temp_cur
1.清空日志
DUMP TRANSACTION 库名 WITH NO_LOG
2.截断事务日志:
BACKUP LOG 数据库名 WITH NO_LOG
3. DBCC SHRINKFILE(1) 其中的1、2是SQL中的mdf 和ldf文件
可是日志文件还是那么大!求助?
把库设置成简单模式。
declare @dbname varchar(50)
declare temp_cur cursor scroll for select name from sysdatabases
open temp_cur
fetch first from temp_cur into @dbname
while @@fetch_status =0
begin
exec ('backup log '+@dbname+' with no_log')
exec ('dbcc shrinkdatabase('+@dbname+')')
exec ('dbcc checkcatalog ('+@dbname+')')
exec ('dump transaction '+@dbname+' with no_log')
fetch next from temp_cur into @dbname
end
close temp_cur
deallocate temp_cur
1.清空日志
DUMP TRANSACTION 库名 WITH NO_LOG
2.截断事务日志:
BACKUP LOG 数据库名 WITH NO_LOG
3. DBCC SHRINKFILE(1) 其中的1、2是SQL中的mdf 和ldf文件
可是日志文件还是那么大!求助?
DUMP TRANSACTION 库名 WITH NO_LOG
2.截断事务日志:
BACKUP LOG 数据库名 WITH NO_LOG
3. DBCC SHRINKFILE(1) 其中的1、2是SQL中的mdf 和ldf文件
如果1表示mdf
你就填2
因为你要清理的是日志
或者直接
DBCC SHRINKFILE('数据库名_log')
select log_reuse_wait_desc,* from sys.databases
where name='你的库名字'再贴一下这个结果:
dbcc loginfo再贴一下这个结果(只贴你log大的那个数据库的):
DBCC SQLperf(Logspace)
EXEC sp_detach_db @dbname = 'RedacsDB' --删除日志文件后,重新挂接数据库
EXEC sp_attach_single_file_db @dbname = 'RedacsDB',
@physname = 'D:\Data\RedacsDB.mdf'
1.设置为简单模式(如果是完整模式,那么需要先备份一下BACKUP LOG 数据库名字 to disk='d:\2.bak'
)
2.DBCC SHRINKFILE (数据库名_log,10)
“数据库名_log” 查看对应的日志逻辑名字
10表示收缩到10M
第1个语句结果是
log_reuse_wait_de.. name database_id source_database_..
REPLICATION chnyo_his 5 null
tempdb,0.4921875,43.94841,0
model,0.4921875,53.96825,0
msdb,0.4921875,57.93651,0
chnyo_his,49489.36,99.87882,0
oldhis,0.484375,59.77822,0
his_old,0.9921875,48.47441,0
his_emr,0.9921875,39.56693,0
his_old3,0.9921875,53.64173,0
his_emr1,0.9921875,38.33661,0
chnyo_his_cs,0.484375,74.89919,0
his_mh,0.484375,45.3629,0
http://www.chnyo.com/mohe.rar
第三个结果导出txtmaster,0.4921875,62.69841,0
tempdb,0.4921875,43.94841,0
model,0.4921875,53.96825,0
msdb,0.4921875,57.93651,0
chnyo_his,49489.36,99.87882,0
oldhis,0.484375,59.77822,0
his_old,0.9921875,48.47441,0
his_emr,0.9921875,39.56693,0
his_old3,0.9921875,53.64173,0
his_emr1,0.9921875,38.33661,0
chnyo_his_cs,0.484375,74.89919,0
his_mh,0.484375,45.3629,0
这个应该是问题所在,因为有replicaction,发布订阅传不下去,导致不能截断日志。
第二个结果也证明了这一点。所以要解决这个问题,先看一下这个库是不是有发布订阅。
如果有,看看为什么推不下去。
如果没有,那么说明这个库曾经做过发布订阅,但未安正常步骤删除,现在需要特殊处理。先看看有没有发布订阅吧。