清空日志

Create proc sp_DumpLog
as
Begin
 /*
 2007-03-15
 清空截断日志，使日志大小保持在500K左右
 */
 declare @DBName varchar(20)
 declare @SQL varchar(8000)
 
 set @SQL =''
 declare C cursor for
 select name
 from master..sysdatabases
 where dbid >6
 open C
 fetch next from C into @DBName
 while @@fetch_status = 0
 Begin
  set @SQL = @SQL + 'dump transaction '+ @DBName + ' with no_log'+char(10)
  set @SQL = @SQL + 'Backup log '+@DBName+' with no_log'+char(10)
  set @SQL = @SQL + 'dbcc shrinkdatabase('''+@DBName+''')'+char(10)
  set @SQL = @SQL + 'exec sp_detach_db ''' +@DBName + ''''+char(10)
  set @SQL = @SQL + 'exec sp_attach_single_file_db  @dbname='''+@DBName+''',@physname =''c:/Program Files/Microsoft SQL Server/MSSQL/Data/'+@dbname+'.mdf'''+char(10)
  fetch next from C into @DBName
 End
 close C
 deallocate C
 print @SQL
 exec(@SQL)
End