In some situations a scheduled shrink of the log files is needed to release disk space. The script bellow can be scheduled in SQL Server Agent Job.
Create database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE [master]; GO IF (DB_ID('PL') IS NOT NULL) BEGIN ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE PL; END GO CREATE DATABASE PL; GO USE PL; GO |
Create test table
|
1 2 |
CREATE TABLE PL.dbo.PL__Test__1 (ID INT IDENTITY(1, 1) PRIMARY KEY, FirstName VARCHAR(32)); GO |
Check Log files sizes (will be reused)
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT DB_NAME(MF.database_id) AS DatabaseName , MF.Name AS LogicalName , MF.Physical_Name AS PhysicalName , (MF.size*8)/1024 [Size (MB)] , D.recovery_model_desc AS RecoveryModelDescription FROM sys.master_files AS MF JOIN sys.databases AS D ON MF.database_id = D.database_id WHERE MF.type_desc = 'LOG'; |
Fill the log file with fake data
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @Counter INT = 1; BEGIN TRAN WHILE (@Counter <= 300000) BEGIN INSERT PL.dbo.PL__Test__1 (FirstName) SELECT 'Peter'; SET @Counter += 1; END COMMIT TRAN |
Check Log files sizes (run the script above)
And the script to shrink the log files
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
USE [master]; DECLARE @C_DatabaseName SYSNAME , @C_LogicalName SYSNAME , @C_RecoveryModelDescription SYSNAME , @DynamicSQL1 VARCHAR(MAX) = ''; DECLARE @ShrinkLogFileCursor CURSOR; SET @ShrinkLogFileCursor = CURSOR FOR SELECT D.name AS DatabaseName , MF.Name AS LogicalName , D.recovery_model_desc AS RecoveryModelDescription FROM sys.master_files AS MF JOIN sys.databases AS D ON MF.database_id = D.database_id WHERE MF.type_desc = 'LOG' -- Only log files -- AND MF.Physical_Name LIKE 'D:\Databases\%' -- Specifed directory -- AND D.name = '' -- Specifed database ; OPEN @ShrinkLogFileCursor; FETCH NEXT FROM @ShrinkLogFileCursor INTO @C_DatabaseName, @C_LogicalName, @C_RecoveryModelDescription; WHILE @@FETCH_STATUS = 0 BEGIN -- Cursor loop -- USE SET @DynamicSQL1 = 'USE ' + QUOTENAME(@C_DatabaseName) + '; '; -- ALTER DATABASE to SIMPLE IF (@C_RecoveryModelDescription = 'FULL') BEGIN SET @DynamicSQL1 += ' ALTER DATABASE ' + QUOTENAME(@C_DatabaseName) + ' SET RECOVERY SIMPLE; '; END -- DBCC SHRINKFILE to 1 MB SET @DynamicSQL1 += ' DBCC SHRINKFILE (' + QUOTENAME(@C_LogicalName) + ', 1); '; -- ALTER DATABASE to FULL IF (@C_RecoveryModelDescription = 'FULL') BEGIN SET @DynamicSQL1 += ' ALTER DATABASE ' + QUOTENAME(@C_DatabaseName) + ' SET RECOVERY FULL; '; END EXEC (@DynamicSQL1); /* print ' ------------------------- Start -------------------------' print @DynamicSQL1 print '-- ' + @C_DatabaseName + ' | ' + @C_LogicalName + ' | ' + @C_RecoveryModelDescription print '------------------------- End -------------------------' */ FETCH NEXT FROM @ShrinkLogFileCursor INTO @C_DatabaseName, @C_LogicalName, @C_RecoveryModelDescription; END -- Cursor loop CLOSE @ShrinkLogFileCursor; DEALLOCATE @ShrinkLogFileCursor; |
The databases with recovery model = FULL are altered to SIMPLE, shrinked and altered back to FULL as explained in MSDN.
Check Log files sizes (run the script above)
Keep it simple :-)