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

30. August 2011 10:21 by Eric in SharePoint, SQL Server  //  Tags: ,   //   Comments (16)

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

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!

Comments (16) -

Todd Wilder
Todd Wilder
3/11/2013 3:48:13 AM #

Awesome! I love it when I copy/paste my way to solving my problems. Its perfect.

Todd Wilder
Todd Wilder
3/11/2013 3:48:13 AM #

Awesome! I love it when I copy/paste my way to solving my problems. Its perfect.

Todd Wilder
Todd Wilder
3/11/2013 3:48:13 AM #

Awesome! I love it when I copy/paste my way to solving my problems. Its perfect.

Todd Wilder
Todd Wilder
3/11/2013 3:48:13 AM #

Awesome! I love it when I copy/paste my way to solving my problems. Its perfect.

Todd Wilder
Todd Wilder
3/11/2013 3:48:13 AM #

Awesome! I love it when I copy/paste my way to solving my problems. Its perfect.

Todd Wilder
Todd Wilder
3/11/2013 3:48:13 AM #

Awesome! I love it when I copy/paste my way to solving my problems. Its perfect.

Todd Wilder
Todd Wilder
3/11/2013 3:48:13 AM #

Awesome! I love it when I copy/paste my way to solving my problems. Its perfect.

Todd Wilder
Todd Wilder
3/11/2013 3:48:13 AM #

Awesome! I love it when I copy/paste my way to solving my problems. Its perfect.

Ameliewallace
Ameliewallace
4/29/2012 7:55:05 PM #

I have to get across my appreciation for your kind support to persons that need help regarding this idea. Your own dedication to passing these points across came to be particularly insightful and have frequently permitted those just like me to achieve their ambitions. Your amazing helpful key points means this much a person like me and far more to my colleagues.

http://www.diskdoctors.com/hard-drive-recovery.asp

Ameliewallace
Ameliewallace
4/29/2012 7:55:05 PM #

I have to get across my appreciation for your kind support to persons that need help regarding this idea. Your own dedication to passing these points across came to be particularly insightful and have frequently permitted those just like me to achieve their ambitions. Your amazing helpful key points means this much a person like me and far more to my colleagues.

http://www.diskdoctors.com/hard-drive-recovery.asp

Ameliewallace
Ameliewallace
4/29/2012 7:55:05 PM #

I have to get across my appreciation for your kind support to persons that need help regarding this idea. Your own dedication to passing these points across came to be particularly insightful and have frequently permitted those just like me to achieve their ambitions. Your amazing helpful key points means this much a person like me and far more to my colleagues.

http://www.diskdoctors.com/hard-drive-recovery.asp

Ameliewallace
Ameliewallace
4/29/2012 7:55:05 PM #

I have to get across my appreciation for your kind support to persons that need help regarding this idea. Your own dedication to passing these points across came to be particularly insightful and have frequently permitted those just like me to achieve their ambitions. Your amazing helpful key points means this much a person like me and far more to my colleagues.

http://www.diskdoctors.com/hard-drive-recovery.asp

Ameliewallace
Ameliewallace
4/29/2012 7:55:05 PM #

I have to get across my appreciation for your kind support to persons that need help regarding this idea. Your own dedication to passing these points across came to be particularly insightful and have frequently permitted those just like me to achieve their ambitions. Your amazing helpful key points means this much a person like me and far more to my colleagues.

http://www.diskdoctors.com/hard-drive-recovery.asp

Ameliewallace
Ameliewallace
4/29/2012 7:55:05 PM #

I have to get across my appreciation for your kind support to persons that need help regarding this idea. Your own dedication to passing these points across came to be particularly insightful and have frequently permitted those just like me to achieve their ambitions. Your amazing helpful key points means this much a person like me and far more to my colleagues.

http://www.diskdoctors.com/hard-drive-recovery.asp

Ameliewallace
Ameliewallace
4/29/2012 7:55:05 PM #

I have to get across my appreciation for your kind support to persons that need help regarding this idea. Your own dedication to passing these points across came to be particularly insightful and have frequently permitted those just like me to achieve their ambitions. Your amazing helpful key points means this much a person like me and far more to my colleagues.

http://www.diskdoctors.com/hard-drive-recovery.asp

Ameliewallace
Ameliewallace
4/29/2012 7:55:05 PM #

I have to get across my appreciation for your kind support to persons that need help regarding this idea. Your own dedication to passing these points across came to be particularly insightful and have frequently permitted those just like me to achieve their ambitions. Your amazing helpful key points means this much a person like me and far more to my colleagues.

http://www.diskdoctors.com/hard-drive-recovery.asp

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Shop our huge range of women fashion accessories online, including necklaces, earrings, bracelets, rings and apple accessories.