Blog Post

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 & 0×40) <> 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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating