March 27, 2018 at 12:40 pm
Hi,
Can somebody help me with my question below:
We have some packages that run every minute and write a lot of SSIS execution history to SSIS DB. This is causing space issues. I tried to minimize logging by enabling only for on error and on task failed events.
But that is still logging for successful executions. We are using SSIS log provider for text files(default)
My intent is to disable logging for successful executions that would alleviate the situation. Can somebody please help me!
Thanks in advance!
Any help is highly appreciated!
March 27, 2018 at 1:36 pm
I would recommend adjusting the configuration for the catalog first - instead of removing the logging. Also - if you are running into space issues with SSISDB then you should increase the space available or move the database to a different drive.
Right-Click on SSIDB under the 'Integration Services Catalog' and select properties. In this dialog, you have the following options:
Clean Logs Periodically - should be set to True
Retention Period (days) - defaults to 365, decrease if needed
Server-wide Default Logging Level - defaults to Basic but can be set to different values or even None (see: https://docs.microsoft.com/en-us/sql/integration-services/performance/integration-services-ssis-logging
Maximum Number of Version per Project - defaults to 10
Periodically Remove Old Versions - set to True
You can adjust the level of logging on a package basis - and those packages that run every minute can be set to None or Performance to reduce how much they log - or use a custom provider to log only what you want.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 28, 2018 at 10:59 pm
Jeffrey Williams 3188 - Tuesday, March 27, 2018 1:36 PMI would recommend adjusting the configuration for the catalog first - instead of removing the logging. Also - if you are running into space issues with SSISDB then you should increase the space available or move the database to a different drive.Right-Click on SSIDB under the 'Integration Services Catalog' and select properties. In this dialog, you have the following options:
Clean Logs Periodically - should be set to True
Retention Period (days) - defaults to 365, decrease if needed
Server-wide Default Logging Level - defaults to Basic but can be set to different values or even None (see: https://docs.microsoft.com/en-us/sql/integration-services/performance/integration-services-ssis-logging
Maximum Number of Version per Project - defaults to 10
Periodically Remove Old Versions - set to TrueYou can adjust the level of logging on a package basis - and those packages that run every minute can be set to None or Performance to reduce how much they log - or use a custom provider to log only what you want.
I think Jeffrery pretty much covered this. You can find the diff types of logging here,
SSIS Logging Levels
None
Basic
Performance
Verbose
RuntimeLineage
April 1, 2018 at 5:21 pm
Jeffrey Williams 3188 - Tuesday, March 27, 2018 1:36 PMI would recommend adjusting the configuration for the catalog first - instead of removing the logging. Also - if you are running into space issues with SSISDB then you should increase the space available or move the database to a different drive.Right-Click on SSIDB under the 'Integration Services Catalog' and select properties. In this dialog, you have the following options:
Clean Logs Periodically - should be set to True
Retention Period (days) - defaults to 365, decrease if needed
Server-wide Default Logging Level - defaults to Basic but can be set to different values or even None (see: https://docs.microsoft.com/en-us/sql/integration-services/performance/integration-services-ssis-logging
Maximum Number of Version per Project - defaults to 10
Periodically Remove Old Versions - set to TrueYou can adjust the level of logging on a package basis - and those packages that run every minute can be set to None or Performance to reduce how much they log - or use a custom provider to log only what you want.
Hi Jeff,
Thanks for your response! I already have an SSIS clean up task configured that has retention period of 14 days in prod and with version history 10 days. I forgot to mention it earlier in my post. But the packages are very complex and log a lot of history. Even with clean up task enabled we have a lot of logging. We will need detailed logging for failed executions but we do not want to log for successful executions. So just wondering if there is a way to disable logging for successful executions
April 2, 2018 at 6:00 am
c.bhuvaneswari - Sunday, April 1, 2018 5:21 PMJeffrey Williams 3188 - Tuesday, March 27, 2018 1:36 PMI would recommend adjusting the configuration for the catalog first - instead of removing the logging. Also - if you are running into space issues with SSISDB then you should increase the space available or move the database to a different drive.Right-Click on SSIDB under the 'Integration Services Catalog' and select properties. In this dialog, you have the following options:
Clean Logs Periodically - should be set to True
Retention Period (days) - defaults to 365, decrease if needed
Server-wide Default Logging Level - defaults to Basic but can be set to different values or even None (see: https://docs.microsoft.com/en-us/sql/integration-services/performance/integration-services-ssis-logging
Maximum Number of Version per Project - defaults to 10
Periodically Remove Old Versions - set to TrueYou can adjust the level of logging on a package basis - and those packages that run every minute can be set to None or Performance to reduce how much they log - or use a custom provider to log only what you want.
Hi Jeff,
Thanks for your response! I already have an SSIS clean up task configured that has retention period of 14 days in prod and with version history 10 days. I forgot to mention it earlier in my post. But the packages are very complex and log a lot of history. Even with clean up task enabled we have a lot of logging. We will need detailed logging for failed executions but we do not want to log for successful executions. So just wondering if there is a way to disable logging for successful executions
Hmmm... did you think about what you are asking for? A package would have to log it all, at least temporarily, until the final piece of the package succeeded, and only then, delete the logs. Kind of dangerous, even if you say you don't need successful logs. If push came to shove, and you had to prove that the package ran successfully at any point, you'd have no proof whatsoever. I think you might want to re-think what you're asking for. I'm pretty sure there's no good way to do this, because even if you were to add some form of "de-logging" step at the end of EVERY job that would decide whether or not to keep the logs, what do you do with the logging of that step? You end up with a proverbial chicken and egg scenario...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply