SqlServer清理数据库日志

来源：互联网发布：网络成瘾原因探析编辑：程序博客网时间：2024/05/17 23:52

在SqlServer数据库数据量提升和大量的操作数据库，不免产生很多的数据库日志，可以通过SqlServer自带的作业进行定期的数据库日志清理，代码如下：

--清空所有数据库日志declare @exec_sql nvarchar(1200),@simple_sql nvarchar(1200),@full_sql nvarchar(1200)  declare @db_name varchar(128)  declare @db_count int  select @db_count=COUNT(1) from sys.databases where database_id >4  declare @j int  set @j =1    while @j<=@db_count   begin          select @db_name=name from (select ROW_NUMBER()         over(order by name)rn,name from sys.databases         where database_id >4 )as tmp where rn=@j         SET @simple_sql=N' use [master] ' + Char(10) + Char(13)        +'ALTER DATABASE ['+@db_name+'] SET RECOVERY SIMPLE WITH NO_WAIT'         EXEC Sp_executesql  @simple_sql,N'@db_name varchar(128)',@db_name                 SET @exec_sql=N'USE ['+@db_name+']'+ Char(10) + Char(13)        +'DBCC SHRINKFILE (N'''+@db_name+'_log'' , 0, TRUNCATEONLY)'          EXEC Sp_executesql  @exec_sql,N'@db_name varchar(128)',@db_name                  SET @full_sql =N' use [master] ' + Char(10) + Char(13)        +'ALTER DATABASE ['+@db_name+'] SET RECOVERY FULL WITH NO_WAIT'        EXEC Sp_executesql  @full_sql,N'@db_name varchar(128)',@db_name                      set @j =@j+1  end

0 0