CODEWITHSUNDEEP
sql-serverssisetlsql-agent
nunopacheco
nunopacheco

Reputation: 71

SSISDB Transaction Log Backup

I have several SSIS packages jobs running, and some months ago my disk got full because the size of the SSISDB database.

I noticed that the cleanup_server_retention_window was set to 365 days, and I changed it to one day. (It is a development server, and at this point I really dont care about the history).

The (big) problem, obviously, is now that the transaction log grows a lot and fast.

To prevent this, I start performing a full backup every week and a transaction log backup every day, and the size of the database is now controlled.

However, some more experienced guys are telling me that this is not the best aproach to this issue, but I cant see any problem with it..

I would like to know if there is a better solution for this.

Upvotes: 0

Views: 5630

Answers (3)

Hans
Hans

Reputation: 79

I fixed this in 3 ways:

  1. adding some missing indexes on the SSISDB database; they should be there after installation of CU4

  2. changed the parameter @delete_batch_size from 1000 to 25 in the stored procedure internal.cleanup_server_retention_window

  3. changed the recoevry model from Full to Simple

Now, when running the SSISDB maintenance job, the transaction log no longer fills up beyond repair causing a database 'crash' / rollback

Upvotes: 0

Mrityunjay Garg
Mrityunjay Garg

Reputation: 56

I tried just about everything including changing retention window; it was deleted the transactions but not reducing the log size. For me the allocated log file size grew to 75 GB. Nothing seemed to help.

The main issue has to do with the recovery model of the SSIS DB that was set to 'Full'. Once I set this to 'Simple' and changed the initial log file size, all was fixed!

I have been monitoring this for the last couple of days just to make sure all is well and it looks fine to me so this operation is safe.

The current log file size is 512KBMB as opposed to 75GB!

Upvotes: 4

TheGameiswar
TheGameiswar

Reputation: 28900

The (big) problem, obviously, is now that the transaction log grows a lot and fast.

You will not see this everyday..The Cause of transaction log growth was changing cleanup_server_retention_window ..when you changed the value from 365 to 1,internally it has to do a lot of deletes

I start performing a full backup every week and a transaction log backup every day, and the size of the database is now controlled

I don't see an issue with backing up SSISD.In our instance ,we changed the recovery model to simple and do daily full backups

Upvotes: 1

Related Questions