February 20, 2017 at 4:10 pm
Hi,
We have an MSDB which has grown to over 10Gb on a SQL 2016 RTM server. At first we thought the issue was related to log entries in the sysssislog table generated by Data Collections - however this is only part of the problem (see this MSDN post: https://social.msdn.microsoft.com/Forums/en-US/eab5c11e-1c2e-483a-abb8-128bd960e713/sql-2016-msdb-database-problem?forum=sqldatabaseengine).
These entries currently only account for 300K out of 25M+ rows. We are running regular purges using both the script provided by MSDN Community Support, and also sp_syscollector_purge_collection_logs.
Does anyone know why there are millions of rows in the sysssislog table, and also suggest how to filter which rows to delete without cleaning out every record over a specific date range?
Thanks,
Matt
MattF
February 22, 2017 at 6:24 am
Maybe because Microsoft wants you to use the Integration Services Catalog 😉 (joking of course)
Are you using the Integration Services Catalog?
February 22, 2017 at 8:15 am
There are no packages deployed to the SSISDB database.
MattF
February 22, 2017 at 8:43 am
MattF - Wednesday, February 22, 2017 8:15 AMThere are no packages deployed to the SSISDB database.
Isn't this table used when the package logging is set to SQL Server? And it can log a lot. I've seen that table grow to over 50 million rows before with logging if it's not maintained. Not sure what scripts are being used to purge the table but most of them I've seen are based on using EndTime in the where clause. Similar to how sp_delete_backuphistory works. Maybe you should post the scripts you're using to purge data from the table.
Sue
February 22, 2017 at 9:06 am
The script provided by MSDN Community Support below targets the rows in sysssislog generated by Data Collections:
USE msdb;
DELETE FROM dbo.sysssislogWHERE source LIKE '%Upload' OR source LIKE '%Collect' OR source LIKE '%Collection';
The sp_syscollector_purge_collection_logs procedure is also running daily. These two scripts only take care of 300K out of 25M+ rows.
Checking whether package logging in SQL Agent jobs is enabled showed it is only turned on for the Data Collections jobs (set to Log to Table, and Append output to existing entry in table). All other maintenance plan jobs and a few Reporting Services subscription jobs have package logging options unchecked.
Any further ideas would be much appreciated.
Matt
MattF
February 22, 2017 at 9:11 am
MattF - Wednesday, February 22, 2017 8:15 AMThere are no packages deployed to the SSISDB database.
Right. What I'm suggesting is that you use the Integration Services Catalog rather than the old way of deploying SSIS packages to msdb.
February 22, 2017 at 9:17 am
I checked the data in sysssislog and there are still a lot of Data Collections rows which the script from MDSN support does not address. Looks like we will have to hit it with a hammer and use a script which purges by EndDate. Hopefully this will be addressed in SP's.
Thanks,
Matt
MattF
February 22, 2017 at 9:20 am
heb1014 - Wednesday, February 22, 2017 9:11 AMMattF - Wednesday, February 22, 2017 8:15 AMThere are no packages deployed to the SSISDB database.Right. What I'm suggesting is that you use the Integration Services Catalog rather than the old way of deploying SSIS packages to msdb.
Sure - there are no SSIS packages deployed to either location (MSDB or SSISDB). All activity in sysssislog is system-generated.
MattF
February 22, 2017 at 9:49 am
MattF - Wednesday, February 22, 2017 9:06 AMThe script provided by MSDN Community Support below targets the rows in sysssislog generated by Data Collections:USE msdb;
DELETE FROM dbo.sysssislogWHERE source LIKE '%Upload' OR source LIKE '%Collect' OR source LIKE '%Collection';
The sp_syscollector_purge_collection_logs procedure is also running daily. These two scripts only take care of 300K out of 25M+ rows.
Checking whether package logging in SQL Agent jobs is enabled showed it is only turned on for the Data Collections jobs (set to Log to Table, and Append output to existing entry in table). All other maintenance plan jobs and a few Reporting Services subscription jobs have package logging options unchecked.
Any further ideas would be much appreciated.
Matt
That looks like it's designed for just the Data Collection logging.
Package logging is a package setting. What you are looking at in the jobs is the output for the jobs, not the same thing. I would guess you have packages logging to the table and it can easily happen without even knowing. You probably want to start going though some of those rows and focus on the source and times. If all your packages run via jobs then try to match the start and end times with certain jobs as well as reviewing the message text.
Sue
February 22, 2017 at 9:55 am
MattF - Wednesday, February 22, 2017 9:17 AMI checked the data in sysssislog and there are still a lot of Data Collections rows which the script from MDSN support does not address. Looks like we will have to hit it with a hammer and use a script which purges by EndDate. Hopefully this will be addressed in SP's.
Thanks,
Matt
And like I said earlier, that's what most people who log to the table do. You'll need to clean it up in chunks to start. Then you can write you own script, job to regularly clean up the table based on whatever the needs are - I've seen it done different ways, such as some people keeping error messages longer than other messages.
Sue
February 22, 2017 at 10:13 am
Thanks Sue its good to know the purging by date can be safely scripted. Its disappointing that an issue like this which can severely impact performance around a system database such as MSDB, has been left unaddressed.
MattF
February 22, 2017 at 10:55 am
MattF - Wednesday, February 22, 2017 10:13 AMThanks Sue its good to know the purging by date can be safely scripted. Its disappointing that an issue like this which can severely impact performance around a system database such as MSDB, has been left unaddressed.
Well, that's not all of what you need to keep an eye on with msdb. Scheduling regular maintenance to address things like backup history, mail history, if you use maintenance plans they have history as well. It's one of those that seems to sneak up and bite everyone eventually. And then you know.
To get some ideas, check the following article and read the comments as well since those address additional areas to monitor:
The Importance of Maintenance on MSDB
It's one of those things that you want to put on your list of things to do if you are responsible for SQL Server. Having scripts for jobs to cleanup msdb is important, especially for the reasons you have noted. In some cases there are stored procedures to help with this - but you still need to schedule them to run regularly. And sometimes, you will need to write your own.
Sue
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply