I'm running SSIS in azure for couple of months now, but I'm getting an error: The database 'SSISDB' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. The SQL Server Agent jobs that are scheduled to clean up the log and project versions are not available in Azure.
The first time it occurred I scaled up the database tier and that solved the problem, but now it reoccurs and I don't want to keep scaling up the database tier. How do I solve this?
Solution
Since there is no SQL Server agent there are no jobs available, but there will be a solution within a couple of weeks. If you can't wait there is a workaround. First make sure the retention period and number of project versions are set to an acceptable level. If you are running and updating packages frequently then the default settings are probably a bit to high.
 |
| The default settings (right click SSISDB to see properties) |
Project versions
I set the Maximom Number of Versions per Project to 3. The clean up the old project version locate the Stored Procedure (SSISDB) [internal].[cleanup_server_project_version] and execute it. There are no parameters.
 |
| Before and after running the Stored Procedure |
Log retention
The same can be done for the Log retention with the stored procedure [internal].[cleanup_server_retention_window]. Again no parameters. The Stored Procedure is working with a T-SQL cursor so if your log is massive and your server very busy it could take a while.
You could even schedule these Stored Procedures with for example Azure Data Factory to clean up regularly, but keep up the SSIS announcements to see when they implement a out-of-the box solution.
No comments:
Post a Comment
Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.
All comments are moderated manually to prevent spam.