Incremental Publish Increasing Transaction Logs

[nbarsley]

We've set up event studio to monitor the changes in the contributor nodes (using the instructions on ykud's website. It works great and its really fast. The only problem is that the transaction logs for our contributor publish table databases seem to be constantly growing at an alarming rate ~1 gig a night. I checked the transaction logs for the content manager last weekend and it had grown from 250 megs to 17 gig's in two days. Has anyone run into this problem?

[ykud]

Is auto-shrink set? (database\properties\options) Set it to on and backup and shrink your transaction log (like explained here, for example).
Also check your Cognos service logging settings (server administration\dispatchers&services) -- it should be basic or none. It seems to me that your problem is somewhere around cognos logging setup.

[praveennb]

Change the Recovery model of the databases to SIMPLE, and ensure that you have good backup schedule.

[SomeClown]

After setting Recovery mode to Simple, you just need to Shrink Database to reclaim the space used in the transaction log.

Since a publish datastore is not data of record, you don't need the same level of recovery on it (your recovery method would be to simply republish the application).  Thus, recovery mode SIMPLE is fine (in case a DBA is asking).

[alaussie]

Is auto-shrink set? (database\properties\options) Set it to on and backup and shrink your transaction log (like explained here, for example).
Also check your Cognos service logging settings (server administration\dispatchers&services) -- it should be basic or none. It seems to me that your problem is somewhere around cognos logging setup.

how de i get access to ykuds website for setting up event studio on contributor nodes

[jeffowentn]

I know this is an old thread, but we are having issues with transactional log databases getting way too large in a short period of time.  This happens on a publish run from a macro and also on an admin link run from a macro.  I looked at trying to set the properties for logging in our 8.4.1 instance of Cognos (SQL 2005 db), but the options I see are:

minimal
basic
request
trace
full

I don't see "none" as suggested in this thread.

We are also seeing transactional log databases that are 16 Gb, when the application database is 9 Gb, or 16 Gb when the app is 685 Mb.  Does this seem right?  Does anyone have suggestions on what to do?  I've passed on the article YKUD suggested related to shrinking transactional log databases, but that appears to be a SQL activity. I would prefer to understand the root cause of this issue and prevent it in the first place, rather than treating the symptom...

Thoughts?

What else do you need from me to provide feedback?

Jeff

[SomeClown]

... but that appears to be a SQL activity.

As you point out, it's a database function and not something inherent to the Cognos products.  Put simply, database transaction logging is taking a copy of each change posted to the database (insert, delete, update) and saving it in the log.  Database logging is for point-in-time recovery (e.g. put it back to the way it was at 1:40 PM today, just before the crash). 

Remedy:  turn off logging on the database (set Recovery Mode property to Simple).  Shrink the database after doing that.  You'll likely need to make a business case to the DBAs for doing this.  For publish containers, you can tell them it's not data of record, and a publish database can be recreated by running another publish.   For application databases, you would need to agree that your recovery point would be to restore from the prior night's backup.