MSSQL清理所有用戶資料庫日誌(SQLSERVER2008)

USE [master];
SET NOCOUNT ON;

DECLARE @tbName VARCHAR(50)='';
DECLARE @tbLog VARCHAR(100)='';
DECLARE @clearSql VARCHAR(MAX)='';

DECLARE @TMP_WHILE_ID INT=0;
SELECT TMP_WHILE_ID=IDENTITY(INT,1,1),TMP_WHILE_FLAG=0,
T.tbName,T.tbLog
INTO #TMP_WHILE
FROM
(
SELECT master.sys.databases.name AS tbName, master.sys.master_files.name AS tbLog
FROM master.sys.master_files INNER JOIN master.sys.databases ON master.sys.master_files.database_id = master.sys.databases.database_id
WHERE (master.sys.databases.owner_sid <> 0x01) AND (master.sys.master_files.type = 1)
) AS T

SELECT @TMP_WHILE_ID=MIN(TMP_WHILE_ID) FROM #TMP_WHILE WHERE TMP_WHILE_FLAG=0;
WHILE @TMP_WHILE_ID IS NOT NULL
BEGIN
    SELECT @tbName=tbName,@tbLog=tbLog FROM #TMP_WHILE WHERE TMP_WHILE_ID=@TMP_WHILE_ID;
    
    SET @clearSql=@clearSql+'USE [master];'+CHAR(10);
    SET @clearSql=@clearSql+'ALTER DATABASE ['+@tbName+'] SET RECOVERY SIMPLE WITH NO_WAIT;'+CHAR(10);
    SET @clearSql=@clearSql+'ALTER DATABASE ['+@tbName+'] SET RECOVERY SIMPLE;'+CHAR(10);
    SET @clearSql=@clearSql+'USE ['+@tbName+'];'+CHAR(10);
    SET @clearSql=@clearSql+'DBCC SHRINKFILE ('''+@tbLog+''',1,TRUNCATEONLY);'+CHAR(10);
    SET @clearSql=@clearSql+'USE [master];'+CHAR(10);
    SET @clearSql=@clearSql+'ALTER DATABASE ['+@tbName+'] SET RECOVERY FULL WITH NO_WAIT;'+CHAR(10);
    SET @clearSql=@clearSql+'ALTER DATABASE ['+@tbName+'] SET RECOVERY FULL;'+CHAR(10);
    
    UPDATE #TMP_WHILE SET TMP_WHILE_FLAG=1 WHERE TMP_WHILE_ID=@TMP_WHILE_ID;
    SELECT @TMP_WHILE_ID=MIN(TMP_WHILE_ID) FROM #TMP_WHILE WHERE TMP_WHILE_FLAG=0 AND TMP_WHILE_ID>@TMP_WHILE_ID;
END

DROP TABLE #TMP_WHILE;
--PRINT @clearSql;
EXEC(@clearSql);