ALTER proc [dbo].[proc_sys_ClearDBLog]
as
begin
set nocount on
declare @dbname varchar(200)
declare cur_db cursor for
select [name] from master.dbo.sysdatabases where
[name] not in( 'tempdb','northwind','model','master','msdb','Resource','VM2009', 'VM2009_BK', 'VM2010')
open cur_db
fetch next from cur_db into @dbname
while @@FETCH_STATUS = 0
begin
--1.清空日志
exec('DUMP TRANSACTION [' + @dbname + '] WITH NO_LOG')
--2.截断事务日志：
exec('BACKUP LOG [' + @dbname + '] WITH NO_LOG')
--3.收缩数据库文件(如果不压缩,数据库的文件不会减小
exec('DBCC SHRINKDATABASE([' + @dbname + '])')
fetch next from cur_db into @dbname
end
close cur_db
DEALLOCATE cur_db
set nocount off
end
