SQL Server:Shrinking All dbs Transaction Log (Transaction Log Shrinking) use script

 Predict the DBCC SHRINK DATABASE finish time

SELECT session_id,percent_complete,DATEADD(MILLISECOND,estimated_completion_time, CURRENT_TIMESTAMP) Estimated_finish_time, (total_elapsed_time/1000)/60 Total_Elapsed_Time_MINS , DB_NAME(Database_id) Database_Name ,command,sql_handle

Method:1
Code to shrink the all db's transaction log files:

Step1: Before processing to next step,Enable xp_cmdshell option so that OS commands can be execute through SSMS.

-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO

Step2: Script File: ShrinkTransactionLog.sql

Copy and paste the below content in ShrinkTransactionLog.sql. Make sure you run the script once in SSMS.

--eazybi_jira --Trim DECLARE @DBName AS NVARCHAR(100), @LogFileName AS NVARCHAR(100), @exec_stmt nvarchar(625) SET NOCOUNT ON --——————————————————————————- --create the temporary table to hold the log file names --—————————————————————————– --Drop table #logfiles CREATE TABLE #logfiles ( dbname NVARCHAR(100), filename NVARCHAR(100), ) --—————————————————————————– --select all dbs, except for system dbs --—————————————————————————– DECLARE curDBName CURSOR FOR SELECT [name] FROM master.sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') AND state_desc = 'ONLINE' --——————————————————————————- --add the log file name to the temporary table,groupid=0 is for logfile and 1 for datafile. --—————————————————————————– OPEN curDBName FETCH NEXT FROM curDBName INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @exec_stmt = 'INSERT INTO #logfiles SELECT ''' + @DBName + ''' , name FROM ' + quotename(@DBName, N'[') + N'.dbo.sysfiles WHERE groupid = 0' EXECUTE (@exec_stmt) FETCH NEXT FROM curDBName INTO @DBName END CLOSE curDBName DEALLOCATE curDBName --SELECT * FROM #logfiles ------------------------------------------------ --select all log filenames from the #logiles ------------------------------------------------- DECLARE curLogName CURSOR FOR SELECT dbname, [filename] FROM #logfiles --—————————————————————————- --shrink all log files --—————————————————————————– OPEN curLogName FETCH NEXT FROM curLogName INTO @DBName, @LogFileName WHILE @@FETCH_STATUS = 0 BEGIN --SELECT @exec_stmt = ' USE ' + quotename(@DBName) + N' DBCC SHRINKFILE (' + quotename(@LogFileName)+')' SELECT @exec_stmt = 'USE [' + @DBName + '] ' + 'ALTER DATABASE [' + @DBName + '] SET RECOVERY SIMPLE WITH NO_WAIT ' + 'DBCC SHRINKFILE(N''' + @LogFileName + ''', 1) ' + 'ALTER DATABASE [' + @DBName + '] SET RECOVERY FULL WITH NO_WAIT' print (@exec_stmt) EXECUTE (@exec_stmt) FETCH NEXT FROM curLogName INTO @DBName, @LogFileName END CLOSE curLogName DEALLOCATE curLogName --—————————————- --clean up the logfile table --—————————————- DROP TABLE #logfiles

Step3: Script to Iterate through All SQL Instances

There are many ways to execute SQL’s or T-SQL’s across listed SQL Instances.
A script or SQL’s need to be executed across ‘N’ number of instances. This example which does just one way activity. In my upcoming post, I’ll explain how to pull and store the data on a centralized server.

This component is enabled where you are intended to run the script(Centralized Server).
In this example, 

1. servernames are listed in C:\servers.txt 

2. T-SQL’s are in c:\ShrinkTransactionLog.sql..

After enabling XP_CMDSHELL and placing both the files on Centralized Server, 

open SSMS(Management Studio) and execute the below script

Input File:

List the servernames under Servers.txt

Copy and paste the below content in servers.txt .

KPDBQ001 KPDBQ002

Open SSMS and execute below T-SQL

Master..xp_cmdshell ‘for /f %j in (c:\servers.txt) do sqlcmd -S %j -i c:\Transaction.sql -E >>c:\ShrinkOutput.txt’

Output : ShrinkOutput.txt

if ‘?’ <> ‘master’ and ‘?’ <> ‘msdb’ and ‘?’<>’tempdb’ and ‘?’ <> ‘model’ BEGIN USE [?]; SELECT ‘?’; DECLARE @LogFile varchar (30); SELECT @LogFile = name FROM dbo.sysfiles WHERE (status & 0×40) <> 0; dbcc shrinkfile (@LogFile); END

(1 rows affected)

-----------------------------------------------------------------------------------------------------------------------------------------------------

Method:2

Before Shining Transaction Log Size

SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, size,(size*8)/1024 SizeMB FROM sys.master_files order by size desc

However, in my case, an enterprise edition SharePoint 2010 server has more than 32 databases, so that's why I quite reluctantly execute the script repeatedly for that many databases, not to mention, it's quite easy to case human error as well.

First off, I created a temp table #TransactionLogFiles, by which I store all the database and log files name in there, so that I could shrink the log files later on:

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','distribution')

Then, after knowing the database name, I could quickly work out the corresponding log files name and insert all their details into the temp table #TransactionLogFiles I created in the previous step by utilizing a SQL Cursor

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

Lastly, iterate each row from the temp table #TransactionLogFiles, then change the database recovery mode to "simple", pass the checkpoint, then shrink the corresponding log file, switch back the database recovery mode back to "full" once the truncation has completed.

-- 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

After Shining Transaction Log Size

SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, size,(size*8)/1024 SizeMB FROM sys.master_files order by size desc