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  //   Comments (0)

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!

SharePoint 2010 Tips

14. August 2011 15:24 by Eric in SharePoint, SQL Server  //   Comments (0)

Just came across those SharePoint 2010 tips lately, and would like to share with everyone if interested:

At its core, SharePoint Foundation is a provisioning engine - Inside Microsoft SharePoint 2010

"STS" stands for "SharePoint Team Services" - SharePoint 2010 Professional Administration

"vti" stands for Vermeer Technologies, Inc., the company that created FrontPage - Various sources

20 Web applications per SP2010 farm is a reasonable limit - Spencer Harbars

The client-side object model was introduced in SharePoint 2010. - Inside Microsoft SharePoint 2010

SharePoint 2010 projects must be configured to target .NET Framework 3.5 - Inside Microsoft SharePoint 2010

SharePoint 2010 is 64-bit only, no exceptions. - Technet

Never access the SharePoint configuration database or any of the content databases directly. Use the OM. - Inside Microsoft SharePoint 2010

The MSFT recommended limit of 10 applies to content application pools only, not service endpoints. -Spencer Harbars

16 GB RAM is recommended for DB servers in medium sized SP2010 farms. - Technet

Pre-grow SP2010 content databases for performance reasons. - Technet

A SP2010 Standard FIS license is limited to a single AD domain and related subdomains. - sharepoint.microsoft.com

BLOB caching can be used with host-named site collections in SP2010. - SharePoint Server Caches Overview

Use an XML webpart with XSL to create a RSS feed viewer in SP2010 Foundation. - Dux Raymond

Restoring a SharePoint SQL config DB backup is NOT supported, so ensure you document/backup your farm settings! - Sean McDonough

Look after your SQL tempdb. It is used constantly! - Michael Noel

Splitting your tempdb for performance should be based on physical - not virtual - CPU cores. - Michael Noel

SQL Server 2008 R2 is strongly recommended over RTM for performance reasons. - Michael Noel

Implement a 1:1 ratio of physical to virtual cores in your virtualised SP2010 farm for performance. - Michael Noel

Office 365 represents a shift from capital expenditure to operational expenditure. - Matt Groves

Following an upgrade to SP2010, you can still switch back to V3 visuals using PowerShell. - Todd Klindt

A feature instance is what gets created when a user activates your feature definition. - Inside Microsoft SharePoint 2010

SharePoint creates feature instances with entries in either a content db or the config db depending upon feature activation scope. - Inside Microsoft SharePoint 2010

The only file that is required for every feature definition is the feature manifest file that must be named feature.xml. - Inside Microsoft SharePoint 2010

The 4 possible feature activation scopes are Web, Site, WebApplication and Farm. - Inside Microsoft SharePoint 2010

SharePoint does not allow two versions of a feature definition to be installed side by side within the same farm: Updated versions overwrite earlier versions. - Inside Microsoft SharePoint 2010

The only way to back up your AAM configuration is to document it - Spencer Harbars

SP2010 automates SQL index defragmentation & statistics maintenance - "Database Maintenance for Microsoft® SharePoint® 2010 Products"

The DBCC CHECKDB command should be run on SharePoint DBs once a week. - "Database Maintenance for Microsoft® SharePoint® 2010 Products"

Do not auto-shrink databases or configure a maintenance plan that programmatically shrinks your databases. - "Database Maintenance for Microsoft® SharePoint® 2010 Products"

Shrink a database only when 50% or more of the content in it has been removed by user or administrator deletions. - "Database Maintenance for Microsoft® SharePoint® 2010 Products"

A BLOB cache persists across application pool recycles. - SharePoint Server Caches Overview

BLOBs are immutable. Accordingly, a new copy of the BLOB must be stored for each version. - Technet