Skip to content

Configuring Ms SQL databases for Cognos Enterprise Planning

Just a quick side note — I usually use simple recovery mode in Ms SQL installations because:

  • application databases become inconsistent during administration tasks so having a backup of database in a middle of GTP crash doesn’t give you anything, you’ll have to roll back to GTP start time. So backing up before and after administrative task is better.
  • publish databases are created via bulk load, which gets rather slow with full logging as log files grow very fast.

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 — and it’s oh so common situations — I arrive at the sites with planning databases occupying hundreds of Gb (no, really, I’ve seen another such case just yesterday).

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)