Einfache Prozedur um die Logfiles einer oder aller Datenbanken zu shrinken

use master
go
Create or Alter PROCEDURE dbo.ShrinkAllLogs (@dbname SYSNAME = '%')
AS
BEGIN

DECLARE @sqlcmd VARCHAR (MAX) = '';
DECLARE @cr as char =Char (13)
DECLARE @lf as char = Char (10)
SET NOCOUNT ON;


DECLARE @workvar Table (dbname sysname, dbid int, SizeStartInMB numeric(15,2), SizeEndInMB numeric(15,2), Differenca as SizeStartInMB-SizeEndInMB);


WITH filelist
AS
(
    SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
    FROM sys.master_files
)
INSERT INTO @workvar (dbname, dbid, SizeStartInMB)


SELECT 
    name, database_id,
    (SELECT SUM(SIZE) FROM filelist WHERE TYPE = 1 AND filelist.database_id = db.database_id) LogFileSizeMB
FROM sys.databases db
WHERE database_id > 4
AND NAME LIKE @dbname;
 
SELECT @sqlcmd = CONCAT ( @sqlcmd,  'USE [',  d.NAME,  ']; CHECKPOINT; DBCC SHRINKFILE ([',  f.NAME + ']) with no_infomsgs;' ,  @cr,@lf)
FROM sys.databases d,
     sys.master_files f
WHERE d.database_id = f.database_id
      AND d.database_id > 4 -- keine sysfiles
      AND f.type = 1
      AND d.NAME LIKE @dbname;


PRINT @sqlcmd;
EXEC (@sqlcmd);
 


WITH filelist
AS
(
    SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
    FROM sys.master_files
)

UPDATE w
set w.SizeEndInMB = (SELECT SUM(SIZE) FROM filelist WHERE TYPE = 1 AND filelist.database_id = db.database_id)
FROM @workvar w
inner join sys.databases db on w.dbid = db.database_id
WHERE database_id > 4
AND NAME LIKE @dbname
 
SELECT * FROM @workvar ORDER BY dbname
SET NOCOUNT OFF;
END;