--以下语句是我写的截断当前库日志和收缩文件的语句,执行前最好先备份。
declare @sql varchar(8000)
declare @id int
---------------------------------------------------截断日志
set @sql = '
dump tran ' + db_name() + ' with truncate_only '
exec(@sql)
if @@error <> 0
begin
raiserror 200001 '截断日志失败。'
end
---------------------------------------------------逐个收缩数据文件至最小!
set @id = -1
while exists(select top 1 * from sysfiles where fileid > @id order by fileid)
begin
select top 1 @id = fileid,@sql = name from sysfiles where fileid > @id order by fileid
set @sql = 'dbcc shrinkfile('''+rtrim(@sql)+''',0)'
exec(@sql)
if @@error <> 0
begin
raiserror 200001 '收缩数据库文件时失败。'
end
end
--------------------
declare @sql varchar(8000)
declare @id int
---------------------------------------------------截断日志
set @sql = '
dump tran ' + db_name() + ' with truncate_only '
exec(@sql)
if @@error <> 0
begin
raiserror 200001 '截断日志失败。'
end
---------------------------------------------------逐个收缩数据文件至最小!
set @id = -1
while exists(select top 1 * from sysfiles where fileid > @id order by fileid)
begin
select top 1 @id = fileid,@sql = name from sysfiles where fileid > @id order by fileid
set @sql = 'dbcc shrinkfile('''+rtrim(@sql)+''',0)'
exec(@sql)
if @@error <> 0
begin
raiserror 200001 '收缩数据库文件时失败。'
end
end
--------------------