July 16, 2025 at 3:26 pm
Hello all,
I am hoping that this is an easy question and I'm just being silly, but is there an SP that will cycle non-error logs? What I mean is, I know about sp_cycle_errorlog for cycling the error log, but what about logs like maintenance plan logs? Does this SP cycle those too or is there a different one for that?
The reason I ask is that when I go to review the MP logs or SSIS catalog logs for a specific package, 90% of the time, I only care about the most recent run or sometimes the past 2 runs. Having to wait (sometimes 5+ minutes) for SQL to load everything from the logs is excessive for something that is a 30 second fix. For example, today I had a maintenance plan step fail and i wanted to load the log to read the error message as the SQL Job didn't tell me anything useful. It took so long I decided to look at the maintenance plan manually while I waited for the log to load and fixed the problem before the log had finished reading from log file #3.
I currently only cycle logs on reboot which is bad practice, especially since I rarely reboot the systems. It is on my to do list to cycle the logs with sp_cycle_errorlog, but I am wondering if that will cycle all logs OR just the error log. If it is just the error log, is there a stored procedure that will cycle all logs?
Thanks a bunch!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 17, 2025 at 8:02 am
For SSIS, there is a property "Clean logs periodically" with retention days. This probably creates an "SSIS Server Maintenance Job" sql agent job.
For maintenance plans you can create a new maintenance plan that cleans up files/reports older than ....
From Ola's maintenance solution there is also something like "sp_purge_jobhistory" and "sp_delete_backuphistory"
July 21, 2025 at 10:19 pm
Thanks a bunch Jo!
I'm going to check what mine are set to. I suspect they are either default (likely) or high (for no good reason) and I'd like to bump them down to 7 days or so. Anything older than that, we should have noticed much sooner.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 22, 2025 at 10:21 am
check the number of days history retained and the number of projects\packages on the SSIS instance properties, default is 365 days for history and can lead to SSISDB becoming bloated excessively
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 22, 2025 at 2:36 pm
Oof... I would almost bet real money it was set to default... BUT glad I didn't - it is set to 7 days. That makes me feel better.
Now I just need to tune my "Clear Backup Reports" maintenance plan on my systems that are still using maintenance plans... or ditch that and migrate everything over to the scripts I wrote. Probably easier to migrate to the scripts BUT I do need to figure out the correct way to handle cleaning up the job history via scripts. Could implement Ola's scripts instead of mine, but they just have sooooo much stuff in them that I'll never use that I am not sure it is worth it for me when I can write my own, short, targeted scripts that I can support...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 22, 2025 at 3:28 pm
Oof... I would almost bet real money it was set to default... BUT glad I didn't - it is set to 7 days. That makes me feel better.
Now I just need to tune my "Clear Backup Reports" maintenance plan on my systems that are still using maintenance plans... or ditch that and migrate everything over to the scripts I wrote. Probably easier to migrate to the scripts BUT I do need to figure out the correct way to handle cleaning up the job history via scripts. Could implement Ola's scripts instead of mine, but they just have sooooo much stuff in them that I'll never use that I am not sure it is worth it for me when I can write my own, short, targeted scripts that I can support...
check number of projects and versions you're retaining too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply