April 4, 2018 at 4:49 pm
Hi,
I'm on SQL Server 2014 (v 12.0.5540.0). We use the SSIS Catalog to house all of our SSIS packages, and we have the retention period set 30 days and a max of 10 project versions. I can see that the versions are in fact limited to 10 by querying the internal.object_versions table. However, I've noticed that the internal.executables has project_version_lsn numbers that go way back (in fact, all the way back to our first lsn #). Why doesn't this table get cleaned up?
The reason I'm asking is because queries like the one posted below are executed several thousand times when we run our ETL, so there is a performance impact if the table just continues to grow:
SELECT
[executable_id]
FROM internal.executables
WHERE project_id = XX
AND project_version_lsn = XX
AND package_name = N'XXX'
AND executable_name = N'XXX'
AND executable_guid = N'XXXX'
AND package_path = N'XXXX';
April 5, 2018 at 3:32 am
chris.o.smith - Wednesday, April 4, 2018 4:49 PMHi,
I'm on SQL Server 2014 (v 12.0.5540.0). We use the SSIS Catalog to house all of our SSIS packages, and we have the retention period set 30 days and a max of 10 project versions. I can see that the versions are in fact limited to 10 by querying the internal.object_versions table. However, I've noticed that the internal.executables has project_version_lsn numbers that go way back (in fact, all the way back to our first lsn #). Why doesn't this table get cleaned up?The reason I'm asking is because queries like the one posted below are executed several thousand times when we run our ETL, so there is a performance impact if the table just continues to grow:
SELECT
[executable_id]
FROM internal.executables
WHERE project_id = XX
AND project_version_lsn = XX
AND package_name = N'XXX'
AND executable_name = N'XXX'
AND executable_guid = N'XXXX'
AND package_path = N'XXXX';
Quick question, is OPERATION_CLEANUP_ENABLED set to TRUE?
😎
SELEC
CCP.*
FROM catalog.catalog_properties CCP;
April 5, 2018 at 7:23 am
Eirikur Eiriksson - Thursday, April 5, 2018 3:32 AMchris.o.smith - Wednesday, April 4, 2018 4:49 PMHi,
I'm on SQL Server 2014 (v 12.0.5540.0). We use the SSIS Catalog to house all of our SSIS packages, and we have the retention period set 30 days and a max of 10 project versions. I can see that the versions are in fact limited to 10 by querying the internal.object_versions table. However, I've noticed that the internal.executables has project_version_lsn numbers that go way back (in fact, all the way back to our first lsn #). Why doesn't this table get cleaned up?The reason I'm asking is because queries like the one posted below are executed several thousand times when we run our ETL, so there is a performance impact if the table just continues to grow:
SELECT
[executable_id]
FROM internal.executables
WHERE project_id = XX
AND project_version_lsn = XX
AND package_name = N'XXX'
AND executable_name = N'XXX'
AND executable_guid = N'XXXX'
AND package_path = N'XXXX';Quick question, is OPERATION_CLEANUP_ENABLED set to TRUE?
😎
SELEC
CCP.*
FROM catalog.catalog_properties CCP;
Yes, OPERATION_CLEANUP_ENABLED is set to TRUE with a RETENTION_WINDOW of 30 days.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply