-- Çalışma yapmadan ÖNCE VLF dosya yapısı hakkında bilgi dökümü alınır -- vlf kutucuklari 1000 sayisini gecmemeli. SELECT [name], s.database_id, COUNT(l.database_id) AS 'VLF Count', SUM(vlf_size_mb) AS 'VLF Size (MB)', SUM(CAST(vlf_active AS INT)) AS 'Active VLF', SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)', COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF', SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)' FROM sys.databases s CROSS APPLY sys.dm_db_log_info(s.database_id) l GROUP BY [name], s.database_id ORDER BY 'VLF Count' DESC GO
-- DB ADINI DEGISTIR !!!!! BEGIN set @path = CONCAT( N'R:\backup\MEGATRON\AtlantisDB\LOG\AtlantisDB_', @gunTar, '.trn') BACKUP LOG [AtlantisDB] TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'AtlantisDB-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5
END
-- Veritabanı logdosyası 0 yakın enküçük değer alana kadar shrink edilir. -- Gerekirse tekrar tekrar çalıştırmalısın. Ben de 153MB kadar düştü ve ileme başladım USE [AtlantisDB] GO DBCC SHRINKFILE (N'AtlantisDB_V2_log' , 0, TRUNCATEONLY) GO use [AtlantisDB] -- Atlantis Log 1GB a kadar düserse alttaki scripti çalıştırıyoruz
USE [master] GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 10GB) PRINT '10GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 20GB) PRINT '20GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 30GB) PRINT '30GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 40GB) PRINT '40GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 50GB) PRINT '50GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 60GB) PRINT '60GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 70GB) PRINT '70GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 80GB) PRINT '80GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 90GB) PRINT '90GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 100GB) PRINT '100GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 110GB) PRINT '110GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 120GB) PRINT '120GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 130GB) PRINT '130GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 140GB) PRINT '140GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 150GB) PRINT '150GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 160GB) PRINT '160GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 170GB) PRINT '170GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 180GB) PRINT '180GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 190GB) PRINT '190GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 200GB) PRINT '200GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 210GB) PRINT '210GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 220GB) PRINT '220GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 230GB) PRINT '230GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 240GB) PRINT '240GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 250GB) PRINT '250GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 260GB) PRINT '260GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 270GB) PRINT '270GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 280GB) PRINT '280GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 290GB) PRINT '290GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 300GB) PRINT '300GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 310GB) PRINT '310GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 320GB) PRINT '320GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 330GB) PRINT '330GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 340GB) PRINT '340GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 350GB) PRINT '350GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 360GB) PRINT '360GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 370GB) PRINT '370GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 380GB) PRINT '380GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 390GB) PRINT '390GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 400GB) PRINT '400GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 410GB) PRINT '410GB YAPILDI' GO ALTER DATABASE [AtlantisDB] MODIFY FILE ( NAME = N'AtlantisDB_V2_log', SIZE = 420GB) PRINT '420GB YAPILDI' GO SELECT [name], s.database_id, COUNT(l.database_id) AS 'VLF Count', SUM(vlf_size_mb) AS 'VLF Size (MB)', SUM(CAST(vlf_active AS INT)) AS 'Active VLF', SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)', COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF', SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)' FROM sys.databases s CROSS APPLY sys.dm_db_log_info(s.database_id) l GROUP BY [name], s.database_id ORDER BY 'VLF Count' DESC GO
