SQL SERVER 2012 数据库日志清理语

--2017719

--SQL SERVER 2012日志清理,其他版本未经测试请慎

DECLARE @ID INT,@NAMES VARCHAR(255),@SQL VARCHAR(500),

@SDBF VARCHAR(255),@SQLS VARCHAR(255),@S VARCHAR(255),@REPT NVARCHAR(255)

SET @SDBF='sys.database_files'

set @ID=7

while @ID<=(SELECT MAX(dbid) FROM dbo.sysdatabases)

BEGIN

select @NAMES=NAME FROM dbo.sysdatabases where dbid=@ID ORDER BY dbid

SET @SQL='USE'+SPACE(1)+'['+@NAMES+']'+SPACE(1)

SET @SQLS=@NAMES+'.'+@SDBF

--EXEC ('SELECT NAME AS NAME FROM ' +@SQLS+ ' WHERE type_desc=''LOG''')

SET @REPT='SELECT @S=NAME FROM ' +@SQLS+ ' WHERE type_desc=''LOG'''

EXEC sp_executesql @REPT,N'@S NVARCHAR(255) OUTPUT',@S OUTPUT;

set @SQL=@SQL+' ALTER DATABASE '+@NAMES +' SET RECOVERY SIMPLE WITH NO_WAIT' 

set @SQL=@SQL+' ALTER DATABASE '+@NAMES +' SET RECOVERY SIMPLE'

set @SQL=@SQL+' USE '+ SPACE(1)+'['+@NAMES+']'+SPACE(1)

set @SQL=@SQL+' DBCC SHRINKFILE (N'''+@S+''' , 11, TRUNCATEONLY)' 

set @SQL=@SQL+' USE '+ SPACE(1)+'['+@NAMES+']'+SPACE(1)

set @SQL=@SQL+' ALTER DATABASE '+@NAMES +' SET RECOVERY FULL WITH NO_WAIT'  

set @SQL=@SQL+' ALTER DATABASE '+@NAMES +' SET RECOVERY FULL'

PRINT (@SQL)

SET @ID=@ID+1 

END