DECLARE @MinSpace INT DECLARE @CurrentSizeMB INT DECLARE @MinimumSpace INT DECLARE @sql varchar(max) DECLARE @DB_NAME VARCHAR(100) SET @DB_NAME = (SELECT DB_NAME())
DECLARE @FileName NVARCHAR(MAX),@FreeSpace INT DECLARE FileNameCur CURSOR FOR
SELECT name AS FileName, CEILING(size/128.0) AS CurrentSizeMB, CEILING(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) AS FreeSpaceMB, CEILING(size/128.0)-CEILING(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) AS MinimumSpace FROM sys.database_files WHERE type IN (0)-- 1 LOG DOSYASI ICINDIR AND CEILING(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)>1000
OPEN FileNameCur FETCH NEXT FROM FileNameCur INTO @FileName,@CurrentSizeMB,@FreeSpace,@MinimumSpace
WHILE @@FETCH_STATUS = 0 BEGIN SET @CurrentSizeMB = (SELECT CEILING(size/128.0) AS CurrentSizeMB FROM sys.database_files WHERE name=@FileName)
SET @MinSpace = (SELECT CEILING(size/128.0)-CEILING(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) FROM sys.database_files WHERE name=@FileName)
WHILE @CurrentSizeMB>=@MinSpace BEGIN DECLARE @C2 INT SET @C2=1000 IF @CurrentSizeMB<@C2 BEGIN PRINT +@FileName+'Shrinklenecek alan yoktur' END ELSE BEGIN SET @sql = 'USE '+@DB_NAME+' GO PRINT ''BAŞLANGIÇ ZAMANI:''+ CONVERT (VARCHAR(30),GETDATE()); PRINT ('''+@FileName+' '+CONVERT(VARCHAR(100),@CurrentSizeMB-@C2)+' Shrink İşlemine Başlandı'') PRINT (''TOTAL: '+@FileName+' '+CONVERT(VARCHAR(100),@FreeSpace)+' Yer Açılacak'') GO DBCC SHRINKFILE (N'''+@FileName+''''+','+CONVERT(VARCHAR(100),@CurrentSizeMB-@C2)+') GO PRINT ''BİTİŞ ZAMANI:''+ CONVERT (VARCHAR(30),GETDATE()); PRINT ('''+@FileName+' '+CONVERT(VARCHAR(100),@CurrentSizeMB-@C2)+' Shrink Edildi'') PRINT (''----------------------------------------------'') GO'
SET @CurrentSizeMB= @CurrentSizeMB - 1000 PRINT (@sql) --EXECUTE (@sql) END FETCH NEXT FROM FileNameCur INTO @FileName,@CurrentSizeMB,@FreeSpace,@MinimumSpace END
