SQL – SQL Server Script to Iterate through All SQL Instances – Example Transaction Log Shrinking

There are many ways to execute SQL’s or T-SQL’s across listed SQL Instances. Most of us might have faced and handled this scenario. 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.

I’ve enabled xp_cmdshell option so that OS commands can be execute through SSMS.

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘xp_cmdShell’, 1;
GO
RECONFIGURE;
GO

This component is enabled where you are intended to run the script(Centralized Server).In this example, servernames are listed in C:\servers.txt and the T-SQL’s are in c:\TransactionLogShrink.sql which contains code to shrink the transaction log files.

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

KPDBQ001
KPDBQ002

Script File:

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

******************************************

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
—————————————————————————–
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)+’)’
–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

********************************************

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 & 0x40) <> 0; dbcc shrinkfile (@LogFile); END

(1 rows affected)

———–
Placeholder
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
—— ———– ———– ———– ———– ————–
5 2 128 128 128 128
DBCC execution completed. If DBCC printed error messages, contact your system administrator.