0

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). Is this normal?

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. It's not clear whether we should be scheduling this or whether it runs automatically (?) but it 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, what "normal" behavior should be, and what we can script as a maintenance task to keep this happening again?

(not sure if this is best posted here or ServerFault, but there were more log shipping questions here!)

James Crowley
  • 3,911
  • 5
  • 36
  • 65

1 Answers1

0

If you're log shipping that often for that period of time, then 30GB is in the right ball-park.

I would take a look at which tables are the worst offenders - this will hint as to where to start clearing down. This SQL gives you a good breakdown of which tables consume which space:

create table #RawData (name varchar(100), rows varchar(20), reservedKB varchar(20), dataKB varchar(20), index_sizeKB varchar(18), unusedKB varchar(18))
create table #Data (name varchar(100), rows int, reserved float, data float, index_size float, unused float)
exec sp_msforeachtable 'insert into #RawData exec sp_spaceused ''?'''
INSERT INTO #Data
SELECT name, rows, 
CONVERT(float, REPLACE(reservedKB, ' KB', ''))/1024, 
CONVERT(float, REPLACE(dataKB, ' KB', ''))/1024, 
CONVERT(float, REPLACE(index_sizeKB, ' KB', ''))/1024, 
CONVERT(float, REPLACE(unusedKB, ' KB', ''))/1024 
FROM #RawData
select *, data+index_size as used from #Data order by data+index_size desc
drop table #RawData
drop table #Data
Andy Joiner
  • 5,932
  • 3
  • 45
  • 72