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!
