Document Details
How To Clean Up Ssisdb Logs With Elastic Jobs
❌
This page contains Windows bias
About This Page
This page is part of the Azure documentation. It contains code examples and configuration instructions for working with Azure services.
Bias Analysis
Bias Types:
⚠️
windows_first
⚠️
windows_tools
⚠️
missing_linux_example
Summary:
The documentation demonstrates a Windows bias by referencing and illustrating the use of SQL Server Management Studio (SSMS) and Object Explorer for configuration, both of which are primarily Windows tools. The instructions for configuring and managing SSISDB log clean-up rely on SSMS and do not mention cross-platform alternatives or command-line options. There are no examples or guidance for users on Linux or macOS, nor is Azure Data Studio (a cross-platform tool) mentioned. PowerShell is used for automation, but no Bash or CLI equivalents are provided.
Recommendations:
- Include instructions for configuring SSISDB log clean-up properties using T-SQL scripts that can be run from any platform, not just via SSMS GUI.
- Mention and provide examples using Azure Data Studio, which is cross-platform, for connecting to and managing SSISDB.
- Provide Azure CLI and Bash script equivalents for the PowerShell automation steps, especially for Elastic Database Jobs setup.
- Explicitly state that SSMS is a Windows-only tool and suggest alternatives for non-Windows users.
- Ensure all code and configuration steps can be performed from Linux/macOS environments, and provide at least one end-to-end example using only cross-platform tools.
Create pull request
Scan History
| Date | Scan ID | Status | Bias Status |
|---|---|---|---|
| 2025-07-12 23:44 | #41 | in_progress | ❌ Biased |
| 2025-07-12 00:58 | #8 | cancelled | ✅ Clean |
| 2025-07-10 05:06 | #7 | processing | ✅ Clean |
Flagged Code Snippets
USE msdb
IF EXISTS(SELECT * FROM sys.server_principals where name = '##MS_SSISServerCleanupJobLogin##')
DROP LOGIN ##MS_SSISServerCleanupJobLogin##
DECLARE @loginPassword nvarchar(256)
SELECT @loginPassword = REPLACE (CONVERT( nvarchar(256), CRYPT_GEN_RANDOM( 64 )), N'''', N'''''')
EXEC ('CREATE LOGIN ##MS_SSISServerCleanupJobLogin## WITH PASSWORD =''' +@loginPassword + ''', CHECK_POLICY = OFF')
ALTER LOGIN ##MS_SSISServerCleanupJobLogin## DISABLE
USE master
GRANT VIEW SERVER STATE TO ##MS_SSISServerCleanupJobLogin##
USE SSISDB
IF EXISTS (SELECT name FROM sys.database_principals WHERE name = '##MS_SSISServerCleanupJobUser##')
DROP USER ##MS_SSISServerCleanupJobUser##
CREATE USER ##MS_SSISServerCleanupJobUser## FOR LOGIN ##MS_SSISServerCleanupJobLogin##
GRANT EXECUTE ON [internal].[cleanup_server_retention_window_exclusive] TO ##MS_SSISServerCleanupJobUser##
GRANT EXECUTE ON [internal].[cleanup_server_project_version] TO ##MS_SSISServerCleanupJobUser##
USE msdb
EXEC dbo.sp_add_job
@job_name = N'SSIS Server Maintenance Job',
@enabled = 0,
@owner_login_name = '##MS_SSISServerCleanupJobLogin##',
@description = N'Runs every day. The job removes operation records from the database that are outside the retention period and maintains a maximum number of versions per project.'
DECLARE @IS_server_name NVARCHAR(30)
SELECT @IS_server_name = CONVERT(NVARCHAR, SERVERPROPERTY('ServerName'))
EXEC sp_add_jobserver @job_name = N'SSIS Server Maintenance Job',
@server_name = @IS_server_name
EXEC sp_add_jobstep
@job_name = N'SSIS Server Maintenance Job',
@step_name = N'SSIS Server Operation Records Maintenance',
@subsystem = N'TSQL',
@command = N'
DECLARE @role int
SET @role = (SELECT [role] FROM [sys].[dm_hadr_availability_replica_states] hars INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] =''SSISDB'')
IF DB_ID(''SSISDB'') IS NOT NULL AND (@role IS NULL OR @role = 1)
EXEC [SSISDB].[internal].[cleanup_server_retention_window_exclusive]',
@database_name = N'msdb',
@on_success_action = 3,
@retry_attempts = 3,
@retry_interval = 3;
EXEC sp_add_jobstep
@job_name = N'SSIS Server Maintenance Job',
@step_name = N'SSIS Server Max Version Per Project Maintenance',
@subsystem = N'TSQL',
@command = N'
DECLARE @role int
SET @role = (SELECT [role] FROM [sys].[dm_hadr_availability_replica_states] hars INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] =''SSISDB'')
IF DB_ID(''SSISDB'') IS NOT NULL AND (@role IS NULL OR @role = 1)
EXEC [SSISDB].[internal].[cleanup_server_project_version]',
@database_name = N'msdb',
@retry_attempts = 3,
@retry_interval = 3;
EXEC sp_add_jobschedule
@job_name = N'SSIS Server Maintenance Job',
@name = 'SSISDB Scheduler',
@enabled = 1,
@freq_type = 4, /*daily*/
@freq_interval = 1,/*every day*/
@freq_subday_type = 0x1,
@active_start_date = 20001231,
@active_end_date = 99991231,
@active_start_time = 0,
@active_end_time = 120000
5. Create your job and add your job step to invoke SSISDB log clean-up stored procedure.
6. Before continuing, make sure you set the specific retention period properly. SSISDB logs outside this period will be deleted and can't be recovered. You can then run your job immediately to start SSISDB log clean-up.