Consider the following information when you plan to shrink a file:
- A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
- Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
- A shrink operation does not preserve the fragmentation state of indexes in the database and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
- Shrink multiple files in the same database sequentially instead of concurrently. Contention on system tables can cause delays due to blocking.
The following script helps you to shrink Log files and it includes the following steps
- Create a Temp Table and hold the entire database names from the database server
- insert all the database name and corresponding log files names into the temp table
- go through each row and execute the shrinkfile script against each database log file on the server.
- clean up
This script will exclude system databases.firstly script will change the Recovery Mode to Simple and shrink log file again set recovery Mode to Full.
CREATE TABLE #TransactionLogFiles (DatabaseName VARCHAR(150), LogFileName VARCHAR(150) ) -- step 1. get hold of the entire database names from the database server DECLARE DataBaseList CURSOR FOR SELECT name FROM SYS.sysdatabases WHERE NAME NOT IN ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB') DECLARE @DataBase VARCHAR(128) DECLARE @SqlScript VARCHAR(MAX) -- step 2. insert all the database name and corresponding log files' names into the temp table OPEN DataBaseList FETCH NEXT FROM DataBaseList INTO @DataBase WHILE @@FETCH_STATUS <> -1 BEGIN SET @SqlScript = 'USE [' + @DataBase + '] INSERT INTO #TransactionLogFiles(DatabaseName, LogFileName) SELECT ''' + @DataBase + ''', Name FROM sysfiles WHERE FileID=2' --SELECT @SqlScript EXEC(@SqlScript) FETCH NEXT FROM DataBaseList INTO @DataBase END DEALLOCATE DataBaseList -- step 3. go through the each row and execute the shrinkfile script against each database log file on the server DECLARE TransactionLogList CURSOR FOR SELECT DatabaseName, LogFileName FROM #TransactionLogFiles DECLARE @LogFile VARCHAR(128) OPEN TransactionLogList FETCH NEXT FROM TransactionLogList INTO @DataBase, @LogFile WHILE @@FETCH_STATUS <> -1 BEGIN SELECT @SqlScript = 'USE [' + @DataBase + '] ' + 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY SIMPLE WITH NO_WAIT ' + 'DBCC SHRINKFILE(N''' + @LogFile + ''', 1) ' + 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY FULL WITH NO_WAIT' EXEC(@SqlScript) FETCH NEXT FROM TransactionLogList INTO @DataBase, @LogFile END DEALLOCATE TransactionLogList --SELECT * FROM #TransactionLogFiles -- step 4. clean up DROP TABLE #TransactionLogFiles