asked
Log shipping - high IO and 30GB MSDB
We've been log shipping a handful of databases for around 6 months now, currently between 20-60GB in size. We are log shipping every 5 minutes, with a 3 day retention. These logs vary from around 18KB to 5MB every 5 minutes (more at the smaller end). We've noticed that the MSDBData database is getting very large (30GB). When we came to delete a (test) log shipped database the other day, it took over 30 minutes, whilst seemingly trying to delete log shipping history. We're now seeing very high IO when the log shipping tasks are enabled. We've tried running sp_cleanup_log_shipping_history, which caused masses of IO for several hours but didn't reduce the size of MSDB (looking at table sizes rather than physical space used on disk), though it does seem to have deleted some rows. As far as I can tell, the time taken to log ship is primarily the call to this SP that is causing issues. Currently the log_shipping_monitor_error_detail table has 15293932 rows and log_shipping_monitor_history_detail has 15350276 rows. The errors were due to insufficient permissions to delete the logs afterwards. Does anyone have any suggestions on how we can diagnose this further and what "normal" behavior should be? Thank you!
I think this is the point you need to resolve > Currently the log_shipping_monitor_error_detail table has 15293932 rows and log_shipping_monitor_history_detail has 15350276 rows. The errors were due to insufficient permissions to delete the logs afterwards. Why doesnt the process have the ability to tidy up after itself? Once that is resolved then things should start to return to normal. With the numbers you are quoting it might take a while though, depending on your hardware and activity on the server
15 People are following this question.
