Applied Dimensionality

Configuring Ms SQL databases for Cognos Enterprise Planning

Posted at — Sep 2, 2010
Configuring Ms SQL databases for Cognos Enterprise Planning

I usually use simple recovery mode in Ms SQL installations because:

In ideal situations, application databases should be in Full Recovery Mode not to lose contributor input and publish databases should be in Simple Recovery mode. But in most cases Simple Recovery Mode and daily backups suffice.

Because otherwise planning databases grow to hundreds of Gb (I’ve seen another such case just the other day).

Here’s a simple script that turns all planning databases (selected by cognos_ep prefix) into Simple Recovery Mode and then shrinks them.

USE master;

DECLARE 
    DBNames CURSOR
FOR
    SELECT
	  NAME 
    FROM sysdatabases
where name like 'cognos_ep%'

OPEN DBNames

DECLARE @Name varchar(50)

FETCH NEXT FROM DBNames
INTO @Name

WHILE (@@FETCH_STATUS <> -1)
BEGIN
	print @Name
	exec ('ALTER DATABASE '+ @Name + ' SET AUTO_SHRINK ON WITH NO_WAIT')
	
	exec('ALTER DATABASE ' +  @Name + ' SET RECOVERY SIMPLE WITH NO_WAIT')
	exec('ALTER DATABASE ' + @Name + ' SET AUTO_SHRINK ON')
	exec('ALTER DATABASE ' + @Name +' SET RECOVERY SIMPLE') 

    DBCC SHRINKDATABASE( @Name , 0)

    FETCH NEXT FROM DBNames
    INTO @Name

END

CLOSE DBNames
DEALLOCATE DBNames

And a bottom line: Worry about backups! Hire a DBA (setting up backups is a one day job — pay part-time)

comments powered by Disqus