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.

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL, T-SQL and tagged , , , , . Bookmark the permalink.

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

  1. Pingback: SSAS Cube Backup strategies and Step by Step approach to setup and configure backup | Prashanth Jayaram

  2. Pingback: SSAS Cube Backup strategies and Step by Step approach to setup and configure backup - SQL Server - SQL Server - Toad World

  3. Hello there, I discovered your website by the use of Google whilst searching for a comparable
    matter, your site came up, it seems to be good. I have bookmarked it in my google bookmarks.

    Hello there, just became alert to your weblog thru Google, and found that it’s truly informative. I’m gonna watch out for brussels.
    I will be grateful in case you proceed this in future.

    Lots of people shall be benefited out of your writing. Cheers!

  4. You have made some decent points there. I checked on
    the internet for more info about the issue and found most individuals will
    go along with your views on this site.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s