----------- Compress the log file in the cyclic database -- R

-- Compress the log file in the circulating database (assuming that the log file fileid = 2)
Create table # db (id int identity (1, 1), name varchar (80), dbsize int, mdfsize int, ldfsize int)
Insert into # db
Select a. name, SUM (B. size) x 8/1024 as 'dbsize [MB] ',
SUM (case when B. type <> 1 then B. size else 0 end) * 8/1024 as 'datafilesize [MB] ',
SUM (case when B. type = 1 then B. size else 0 end) * 8/1024 as 'logfilesize [MB]'
From sys. databases as
Inner join sys. master_files as B on a. database_id = B. database_id
Where a. database_id> 4
Group by a. name
Order by SUM (case when B. type = 1 then B. size else 0 end) -- Sort logs in ascending order
Declare @ min_id int, @ max_id int, @ tmp_db varchar (80), @ s varchar (max)
Select @ min_id = min (id), @ max_id = max (id) from # db where ldfsize> 0 -- specify the MB where the log file is larger
While @ min_id <= @ max_id
Begin
Select @ tmp_db = name, @ min_id = @ min_id + 1 from # db where id = @ min_id
Set @ s = 'backup log' + @ tmp_db + 'with no_log'
Exec (@ s)
Set @ s = 'use' + @ tmp_db + '; dbcc shrinkfile (2 )'
Exec (@ s)
End
Drop table # db