DECLARE @dbname VARCHAR(50)
DECLARE @fname VARCHAR(50)
DECLARE @dbid tinyint
DECLARE @lsize numeric(10,5)
DECLARE @TSQLExec VARCHAR (MAX) = '';
declare db_cur cursor for
SELECT
db.name DBName, fs.name FName, db.database_id,
fs.SIZE * 8.0 / 1024 LogFileSizeMB
FROM sys.databases db
left join sys.master_files fs on fs.database_id = db.database_id
WHERE db.database_id > 4 and db.state=0 and fs.type=1 and db.is_read_only=0
open db_cur
FETCH NEXT FROM db_cur INTO @dbname , @fname, @dbid ,@lsize
WHILE @@FETCH_STATUS = 0
BEGIN
set @TSQLExec = concat('use [', @dbname,'];CHECKPOINT;','dbcc shrinkfile(N''',@fname,''',64) with no_infomsgs;'); --, Char (13),Char (10)
set @TSQLExec = concat(@TSQLExec,'ALTER DATABASE [',@dbname,'] MODIFY FILE ( NAME = N''',@fname,''', FILEGROWTH = 64MB, MAXSIZE = UNLIMITED );');
--set @TSQLExec = concat(@TSQLExec,'ALTER DATABASE [',@dbname,'] MODIFY FILE ( NAME = N''',@fname,''', SIZE = 16MB);');
print @TSQLExec;
--EXEC (@TSQLExec);
FETCH NEXT FROM db_cur INTO @dbname , @fname, @dbid ,@lsize
END
close db_cur
deallocate db_cur