суббота, 26 марта 2022 г.

SQL shrink logfiles

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