August 4, 2019 at 1:18 am
Hello,
so my SQL agent job SSIS Server Maintenance Job has slowly been getting slower and slower over the past week, then today it ran over 8 plus hours when suddenly the SSISDB transaction log filled up max (not the drive Thank GOD) but now the SQL agent job failed and the SSISDB is "In Recovery", i tried to backup the key, but because its in recovery, I cant do anything, the In Recovery process is taking forever, its barely at 1 percent which has been already 3 hours... any ideas what i can do to get this database back? i do have backups from last night... but cant detach, cant restore etc...
August 5, 2019 at 2:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 5, 2019 at 2:52 pm
I believe you have two issues:
(1) Your SSIS package(s) are probaly logging a tremendous amount of event detail.
(2) The above has caused your SSISDB database to swell and then the transaction log fills during daily scheduled retention cleanup.
If you have a recent backup of SSISDB:
- Temporarily suspend your SSISDB maintenance job
- Stop SQL Server
- Delete or move the mdf and ldf files for SSISDB (this will prevent resumption of rollack on next start)
- Re-start SQL Server
- Restore SSISDB from backup
- Confirm your SSISDB maintenance is scheduled to run daily rather than weekly. This will will reduce the volume of data deletion occurring for any given run.
Also, refer to this guide to confirm the logging level of each SSIS package is adequate for you needs while not logging to much detail. For example, if all you are interested in is logging errors and performance metrics, then choose 'Performance' instead of 'Basic'.
https://www.mattmasson.com/2011/12/what-events-are-included-in-the-ssis-catalog-log-levels/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply