Click here to Skip to main content
Click here to Skip to main content

Tagged as

Truncate & Shrink Transaction Log files on SQL Server 2008 by a nifty SQL script

, 28 Sep 2011
Rate this:
Please Sign up or sign in to vote.
Had a task yesterday to restore a few SharePoint 2010 site collection Production databases back to the Development environment. Then turns out that the development SQL Server VM doesn't have enough hard-drive space available for those production humongous sites, after getting confirmed by the system

Had a task yesterday to restore a few SharePoint 2010 site collection Production databases back to the Development environment. Then turns out that the development SQL Server VM doesn't have enough hard-drive space available for those production humongous sites, after getting confirmed by the system administrator that they wouldn't allocate any extra space to that the development environment for my case, thereby I just have to go down the path with truncating & shrinking all those fat transaction log files there.

Well, in terms of how to truncate the transaction log files on SQL Server 2008, there are heaps resource available online, you could simply find the following tips right away by simply Googling it;

SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008

http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/

How to truncate and shirnk Log files in SQL Server 2008

http://madhuottapalam.blogspot.com/2008/05/faq-how-to-truncate-and-shrink.html

My scenario is a bit different in terms of more than 30 SharePoint databases there, and I just don’t want to shrink and truncate their transaction logs individually like how it used to be done. (I just want to be lazy in the productive and reusable way)

By default, the SHRINKFILE script can be simply like this:

USE [WSS_ApplicationUsage]
GO
ALTER DATABASE [WSS_ApplicationUsage] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(N'WSS_ApplicationUsage_log', 1)
ALTER DATABASE [WSS_ApplicationUsage] SET RECOVERY FULL WITH NO_WAIT

GO

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.

On the other side, it's just not reusable approach at all, by which I have to hardcode the database and transaction log file names each time when I need to switch to the different database. (Please don't criticize my laziness).

Based upon all those factors, I created this following script to get my job done gracefully in a reusable and extensible way

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

Just simple like that. All the fatty transaction log files can be truncated within a couple of seconds.

Horray! My job is gracefully done here and hopefully if someone comes across the similar issue later on, they could leverage the script to save a lot time and energy on this Wink | ;)

Extensibility

I always like to put "Extensibility" into consideration when it comes to the development.

If look at the script above carefully, you would find SYS.sysdatabases and sysfiles tables, in other words, if you have any particular condition here, for example, you would like to only shrink the transaction file size above 4GB, so just put the extra Where clause around that, then this script could fully work on for your scenario. Enjoy!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


Comments and Discussions

 
SuggestionPossible Improvements PinmemberMember 394742112-Mar-13 8:33 
GeneralMy vote of 5 PinmemberMunirhussain29-Jan-12 22:06 
QuestionA consideration on shrinking Pinmemberstudioalessi10-Oct-11 0:34 
QuestionWhy not SQL Server agent ? PinmemberVertex13-Oct-11 8:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 28 Sep 2011
Article Copyright 2011 by Eric Xue (brokensnow)
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid