Thanks To Original Thread:
http://capstonebi.blogspot.de/2012/09/ssis-2012-deployment-frustrations.html
https://connect.microsoft.com/SQLServer/feedback/details/804901/ssis-2012-deploying-new-versions-of-large-projects-runs-into-a-timeout-during-deployment-into-ssis-catalog
Failed to deploy
project. For more information, query the operation_messages view for the
operation identifier ’219′. (Microsoft SQL Server, Error: 27203)http://capstonebi.blogspot.de/2012/09/ssis-2012-deployment-frustrations.html
https://connect.microsoft.com/SQLServer/feedback/details/804901/ssis-2012-deploying-new-versions-of-large-projects-runs-into-a-timeout-during-deployment-into-ssis-catalog
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.
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
No comments:
Post a Comment