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 exclusively referencing Windows-based tools (such as SQL Server Management Studio and Azure PowerShell), providing step-by-step instructions for SSMS (a Windows-only application), and omitting any guidance or examples for Linux or cross-platform alternatives. There are no examples using Azure CLI, cross-platform SQL clients, or Linux-native automation tools. The documentation assumes the user is operating in a Windows environment.
Recommendations:
- Include instructions for connecting to SSISDB and configuring properties using cross-platform tools such as Azure Data Studio, Azure CLI, or sqlcmd, which are available on Linux and macOS.
- Provide examples of automating SSISDB log cleanup using bash scripts, Azure CLI, or Python, in addition to PowerShell.
- Mention and demonstrate how to perform relevant tasks using Azure Portal UI, which is OS-agnostic.
- Explicitly state that SSMS is Windows-only and suggest cross-platform alternatives for users on Linux or macOS.
- Ensure that all automation and scripting examples are provided in both PowerShell and bash/Azure CLI where possible.
Create pull request
Scan History
| Date | Scan ID | Status | Bias Status |
|---|---|---|---|
| 2025-09-16 00:00 | #113 | completed | ✅ Clean |
| 2025-09-15 00:00 | #112 | completed | ✅ Clean |
| 2025-09-14 00:00 | #111 | completed | ✅ Clean |
| 2025-09-13 00:00 | #110 | completed | ✅ Clean |
| 2025-09-12 00:00 | #109 | completed | ✅ Clean |
| 2025-09-11 00:00 | #108 | completed | ✅ Clean |
| 2025-09-10 00:00 | #107 | completed | ✅ Clean |
| 2025-09-09 00:00 | #106 | completed | ✅ Clean |
| 2025-09-08 00:00 | #105 | completed | ✅ Clean |
| 2025-08-17 00:01 | #83 | in_progress | ✅ Clean |
| 2025-07-13 21:37 | #48 | completed | ✅ Clean |
| 2025-07-09 13:09 | #3 | cancelled | ✅ Clean |
| 2025-07-08 04:23 | #2 | cancelled | ❌ Biased |
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.