Failed to deploy
project. For more information, query the operation_messages view for the
operation identifier ’219′. (Microsoft SQL Server, Error: 27203)
Solution:
To verify detail error message:
======================
select * from catalog.operation_messages
where operation_id=10069
The message column reads as follows:
Failed to deploy the
project. Fix the problems and try again later.:Timeout expired. The
timeout period elapsed prior to completion of the operation or the server is
not responding.
The statement has been terminated.
As the person deploying the project, I can’t “help” the
Deployment Wizard with tweaking any queries, but I could see what it was doing
using SQL Profiler. The query that
caught my attention is the following:
exec
[internal].[sync_parameter_versions] @project_id=2,@object_version_lsn=10
Notice the number of reads(1.7M) and the duration(30
seconds). I acquired the query plan for
this stored procedure and found that it would run much faster if the following two indexes are applied to
the SSISDB database.
USE [SSISDB]
GO
CREATE NONCLUSTERED INDEX [ix1_internal_object_parameters_inc] ON [internal].[object_parameters]
(
[project_id] ASC ,
[project_version_lsn] ASC
)
INCLUDE ( [parameter_id],
[object_type],
[object_name],
[parameter_name],
[parameter_data_type],
[required],
[sensitive]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF , ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix2_internal_object_parameters_inc] ON [internal].[object_parameters]
(
[project_id] ASC ,
[project_version_lsn] ASC ,
[object_type] ASC ,
[object_name] ASC ,
[parameter_data_type] ASC ,
[required] ASC ,
[sensitive] ASC
)
INCLUDE ( [parameter_name],
[default_value],
[sensitive_default_value],
[value_type],
[value_set],
[referenced_variable_name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF , ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO