Truncation Of Transaction Logs Using SSIS

HI,

What I have set out to do is to try and truncate the transaction logs on my server instances after a nightly backup. I went ahead and used the shrink database option in the maintenance plan wizard. Unfortunately I have found this will only truncate the logs for databases using the simple database recovery model, and I wish to truncate the whole lot to save space...

I went ahead and put together an SSIS package that does the following:

1. First thing I do is build a fresh copy of a database listing in a temp table using the following SQL statement

USE Master;
Go
SELECT name, DATABASEPROPERTYEX(name, 'Recovery') AS RecoveryModel, DATABASEPROPERTYEX(name, 'Status') AS DBStatus INTO TempTables.dbo.Recovery FROM sysdatabases ORDER BY name
GO

2. Next I read this dataset back into memory using a SQL statement as follows:

USE TempTables
GO
Select name from dbo.recovery Where name <> 'master' and name <> 'model' and name <> 'msdb' and name <> 'tempdb'

I map the results to a results set called User::TableName (which is of an object data type)

3. Next the package goes to a ForEach Loop container and I use the Foreach ADO Enumerator Enumerator to read the data I just selected into the look. I select the User::TableName variable and use the enumeration mode 'Rows in first table'. I have used a variable mapping of Variable: User::TableName with an Index 0.

4. I then use the following SQL statement (which sits in the For Each Loop) to try and alter the recovery model:

ALTER DATABASE @TableName
SET RECOVERY SIMPLE;
GO
This is where the package falls over with the following error message

SSIS package "mnt_TransLog.dtsx" starting.
Error: 0x0 at Simple Mode: Incorrect syntax near '@TableName'.
Error: 0xC002F210 at Simple Mode, Execute SQL Task: Executing the query "ALTER DATABASE @TableName
SET RECOVERY SIMPLE;
" failed with the following error: "'RECOVERY' is not a recognized SET option.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Simple Mode
Warning: 0x80019002 at For Each Table Set to Simple: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at mnt_TransLog: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "mnt_TransLog.dtsx" finished: Failure.

I think the problem here is either I am not passing the variable across correctly (i.e. not declaring it or something..!) or I am passing the wrong type of variable across.

Once the recovery model is changed I can truncate the transaction logs successfully but I can€™t seem to until this is done! Any help in finding a solution to this problem or a better way of approaching this problem would be appreciated!

Thanks

Marek Kluczynski
Management Information Officer
Investors in People UK
7-10 Chandos Street
London
W1G 9DQ
Tel: 020 7467 1956