sql server shrink many databases‘s logfile by log file size

set nocount on

declare @databasename nvarchar(128)

declare @logname nvarchar(128)

declare @statement nvarchar(max)

declare csr_database cursor for

select b.name as dbname,c.name as filename

from sys.dm_io_virtual_file_stats(null,null) a,

sys.databases b, sys.master_files c

where a.database_id=b.database_id

and a.file_id=c.file_id

and a.database_id=c.database_id

and c.type=1

and c.database_id > 4

and a.size_on_disk_bytes/1024 > 512000 --大于500M

and b.state=0

and b.recovery_mode=1

order by a.size_on_disk_bytes desc

open csr_database

fetch next from csr_database into @databasename,@logname

while @@fetch_status=0

begin

        set @statement =

                +' use ' + @databasename

                +' declare @i bigint

                +' set @i = 0'

                +' backup database ' + @databasename

                +' to disk = N''nul'''

                +' while @i < 5'

                +' begin'

                +'        backup log  ' + @databasename ' to disk = N''nul'''

                +' checkpoint'

                +' dbcc shrinkfile(' +@logname+',EMPTYFILE)'

                +' set @i = @i+1'

                +' end'

        --select @statement

        exec sp_executesql @statement

        fetch next from csr_database into @databasename,@logname

end

close crs_databases

deallocate crs_databases