SQL 2016 MSDB database very large

Question

Post reply

SQL 2016 MSDB database very large

MattF

SSCrazy

Points: 2285
More actions
December 12, 2016 at 3:07 pm

#332701

Hi,
A SQL 2016 MSDB database has grown very large. There are over 8 million rows in the sysssislog table.
We have run the syspolicy_purge_history job - also the stored procedure "dbo.sp_purge_jobhistory" and the Maintenance plan History Cleanup task with little change in the record count.
We would prefer not to manually delete records as this post suggests: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ef333ad4-d370-4cea-8055-227d402ba65b/sysssislog-maintenance?forum=sqlintegrationservices
It looks as though most of the records in sysssislog are generated by SQL Data Collections.
I also tested this on our SQL 2016 SP1 server and the history purge does not remove the rows generated by SQL Data Collections.
Can anyone replicate this issue?
Thanks,
Matt
MattF

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

kevaburg SSCoach Points: 18131 More actions · Answer 1

Hi Matt,

are you using the SSIS Catalog? If so it may be worth having a look at the Catalog Properties.

The standard value for Retention Days is 365 and when you have a lot going on in the catalog could easily result in overblown logs.

If you reduce this figure and ensure that Clean Logs Periodically is set to "True" the problem will more than likely be resolved.

Regards,

Kev

MattF SSCrazy Points: 2285 More actions · Answer 2

There is no SSISDB catalog - and no SSIS activity other than the Data Collections. Its clear that the MDW is generating the millions of rows - the values for the Source column are: "QueryActivityUpload","PerfCountersUpload" or "TSQLQueryUpload".

MattF

kevaburg SSCoach Points: 18131 More actions · Answer 3

MattF (12/13/2016)
There is no SSISDB catalog - and no SSIS activity other than the Data Collections. Its clear that the MDW is generating the millions of rows - the values for the Source column are: "QueryActivityUpload","PerfCountersUpload" or "TSQLQueryUpload".

OK....I made an assumption based on this table.....

There are over 8 million rows in the sysssislog table

I have never used the MDW but have seen many negative posts relating to its resource intensive nature. I will search them out and post them here....

MattF SSCrazy Points: 2285 More actions · Answer 4

The issue is not the MDW - it is that the rows in MSDB are not cleared up by any of the History Cleanup tasks.

MattF

kevaburg SSCoach Points: 18131 More actions · Answer 5

I have done a little research and not found any setting that can keep this under control. From what I have read up until now though the records contained in the table can be safely deleted.

Although it isn't something you wanted to do would it not be beneficial to simply manually clean the table and add a cleanup Job to syspolicy_purge_history to automate the procedure on a regular Basis?

Another answer to your problem isn't really leaping out to me at the moment.....sorry.

MattF SSCrazy Points: 2285 More actions · Answer 6

Thanks for the suggestion - I have logged this issue with MS Connect - please vote it up here: https://connect.microsoft.com/SQLServer/feedback/details/3116261

In the meantime we will do a manual cleanup as a workaround.

MattF

Perry Whittle SSC Guru Points: 233821 More actions · Answer 7

remove the rows based on your required date range and trim them ongoing would be best idea

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉