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);