When the SSISDB database is created, it appears to be using the "Full" recovery model by default.  Because of the recovery model, the SSISDB transaction log needs to be regularly backed up or I risk running out of disk space.

I would like to set the recovery model to "Simple", so that I do not need to worry about the transaction log consuming too much space.  However, I am not sure what the consequences of that action are.

Could someone explain the consequences and/or the features lost by switching the recovery model of the SSISDB database from "Full" to "Simple"?

Set retention period to lower if you don't want large SSISDB. 

• Right Click SSISDB 
• Properties > Change tracking






Retention period is in days.

Check KB2972285 , it says

* Clean logs periodically (set to True)

* Retention period (set to specific number of days the larger the number of days the more prevalent the problem could be)

* Periodically remove old versions (set to true)

* Maximum number of versions per project

Hi JIMBO,

As per my understanding, SSISDB database just a database, we can treat it as any user db. So we can change its Recovery model from Full to Simple.

Before changing database Recovery model, we need to know the advantage and disadvantage of the Recovery model. Simple recovery model maintains only a minimum amount of information in the SQL Server transaction log file. You will lose any data modifications made between the time of the latest full/differential backup and the time of the failure when you want to restore. Full recovery model provides complete protection against data loss. In the unfortunate case of disaster or application\user error, you can restore to the point-in-time by using the available transaction log backups (assuming your transaction backups are complete up to that point in time). So for production environment, I suggest you use Full Recovery model.

For more details about importance of Recovery Model in SQL Server, please see:
http://www.databasejournal.com/features/mssql/importance-of-recovery-model-in-sql-server.html

Thanks,
Katherine Xiong

Thanks Anuj.  However, I am not looking to clean the logs just yet.  I would rather just change the recovery mode to Simple since I don't see the benefits of using Full recovery on the SSISDB database.  To that end, I am unable to find any Microsoft documentation stating what benefits would be lost when switching the recovery mode to Simple for SSISDB.

Thanks Katherine.  I have a good understanding between the Simple and Full recovery mode options in SQL Server.  What confuses me is that the default recovery mode setting for SSISDB is Full instead of Simple.

One of the biggest selling points of Full recovery is point-in-time recovery.  However, I don't think that feature is necessary for SSISDB since the changes are generally during package execution, administration, or deployment.  While the execution may be scheduled and frequent, the other activities are rare.  In addition, the execution logs and project/package versions are stored in data tables.  For project/package deployments, failed deployments should automatically be rolled back.  I would expect similar behavior in the SSIS execution logs.

I am tempted to just switch the recovery mode to Simple.  Unfortunately, with the default setting being Full, I am concerned that changing the recovery model will inherently break an important SSIS feature.  This led to my initial question: What are the consequences and/or the features lost by switching the recovery model of the SSISDB database from "Full" to "Simple"?  If the answer is just losing point-in-time recovery, then I can go ahead and switch the recovery model to Simple.

SSISDB is just another database, which is created by copying the model database. 

It is not that the SSISDB is set to FULL as a default, it is your model database is set to FULL, so it gets created that way.

Tom, I believe the SSISDB catalog database is created internally using RESTORE instead of CREATE DATABASE so it doesn't honor the model recovery model.

I'm note aware of a reason FULL is a requirement, aside from the usual recovery considerations.  I change the recover model to SIMPLE on our development databases.