Of Ones and Zeros

Truncating Transaction Logs in SQL 2008

Posted on 29. Jan, 2010 by bryan.

One of my clients is beginning to migrate to SQL 2008 for their SharePoint farms.  In the past, we have used scripts to truncate the transaction logs and shrink the DBs in the non-production environments using the “BACKUP LOG [dbname] WITH TRUNCATE_ONLY” command.  As you may be aware, the “WITH TRUNCATE_ONLY” option has been deprecated in SQL 2008.  In SQL 2008 instead you need to change the recovery mode of the database to SIMPLE and then back to FULL in order to truncate the log (or perform a backup of the log of course).  In production environments obviously backing up the transaction log often is the best strategy, however in non-production environments that isn’t always feasible.  I wrote the following script for this client to truncate the log all databases other than the system ones. 

Enjoy!

DECLARE @DatabaseNames TABLE
(
	name varchar(255)
)

INSERT INTO @DatabaseNames
SELECT name FROM sys.databases WHERE
name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )

DECLARE DatabaseCursor CURSOR FOR
SELECT * FROM @DatabaseNames

OPEN DatabaseCursor

DECLARE @DatabaseName varchar(255)
DECLARE @LogFileName varchar(255)
DECLARE @SqlStatement varchar(2000)

FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

WHILE @@FETCH_STATUS=0
BEGIN
	PRINT '----------------------------------------------------------'
	PRINT 'Processing database ' + @DatabaseName
	PRINT '----------------------------------------------------------'

	SET @SqlStatement = 'ALTER DATABASE [' + @DatabaseName +
		'] SET RECOVERY SIMPLE'

	PRINT @SqlStatement
	EXEC (@SqlStatement)

	SET @LogFileName =
		(
			SELECT b.name
			FROM sys.databases a
			INNER JOIN sys.master_files b ON
				a.database_id = b.database_id
			WHERE
				a.name = @DatabaseName
				AND b.name LIKE '%log')

	PRINT @LogFileName

	SET @SqlStatement = 'USE [' + @DatabaseName +
		'] DBCC SHRINKFILE(N''' + @LogFileName + ''', 1)'

	PRINT @SqlStatement
	EXEC (@SqlStatement)

	SET @SqlStatement = 'ALTER DATABASE [' + @DatabaseName +
		'] SET RECOVERY FULL'

	PRINT @SqlStatement
	EXEC (@SqlStatement)

	FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
END

CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Continue Reading

User Profile Import Stuck

Posted on 26. Jan, 2010 by bryan.

A client had an issue today with user profile imports being “stuck”.  In one instance (the production instance) the profile import was stuck actually performing the import, in the non-production instance it was stuck on “Enumerating”.  Found a great article by Henrik Andersson to fix the issue.  Here is what he posted as the fix:

Open the registry editor. Browse to “HKEY_LOCAL_MACHINESOFTWAREMicrosoftOffice Server12.0SearchApplications”. Here you will find a key for each SSP in the farm. Drill down to "GatherProfileImportContentSources" (which is the Full Import) and check the key “CrawlNumberInProgress”. If it´s not “0xffffffff” then the number displayed corresponds to a key under the branch “HKEY_LOCAL_MACHINESOFTWAREMicrosoftOffice Server12.0SearchApplications\GatherProfileImportCrawls”.
I changed the number in the key “CrawlNumberInProgress” to “0xffffffff” and then restarted the “Office SharePoint Server Search” service for the settings to be applied.

Continue Reading