Hi,
we have a 40 gb sql2005 dbase on a sql2005 enterprise server which
must be available 24x7x365;
So I configured logshipping to another server. This works great, every
5 minutes the logfile ships and we have a warm standby server.

BUT...
(and this is something you never read when you read about logshipping
configuration):

Our MSDB dbase is now 110 GB ! I checked which tables are largest:

Exec SP_SpaceUsed log_shipping_monitor_history_detail
reserved: 5978352 KB
data: 288600 KB
rows: 748340

I've killed some data using the:
sp_cleanup_log_shipping_history
which requires the agent_id parameter which i found using : select top
10 * from log_shipping_monitor_history_detail with (nolock)

You also can get large tables related tot he backups themselves. Use
sp_deletebackuphistory to purge the tables on a regular basis. It takes a
Datetime value as a parameter that will tell it to delete all backup history
older than the date you pass. The tables are not indexed and this will be
an extremely slow process to begin with. It may take many hours for this
proc to run the first time. From there on if you run it daily or even weekly
it will only take a second to run and keep the tables in check.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Shuwi" gmail.com> wrote in message
news:be486581-6a1b-496a-a13a-461731e669eb@e39g2000hsf.googlegroups.com...

I've found that applying Geoff's scripts for adding a few indexes to msdb
tables helps quite a bit. The trick is remembering to do it on new
installations and after service packs and other updates. :-)

http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning
.aspx

Hi Andrew,

Thanks a lot for your answer !
I've run the sp_delete_backuphistory @oldest_date = '08-01-2008' but when I
run the

SELECT [Size in MB] = SUM(IDX.reserved)/128,
[Object Name] = OBJ.name
FROM msdb.dbo.sysindexes IDX
JOIN msdb.dbo.sysobjects OBJ
ON OBJ.id = IDX.id
WHERE IDX.indid IN (0, 1, 255)
GROUP BY IDX.id, OBJ.name
ORDER BY 1 DESC

I see that my tables:
47361 MB ->log_shipping_monitor_error_detail
46479 MB ->sysjobhistory
5841 MB ->log_shipping_monitor_history_detail

so, sp_delete_backuphistory seems not to affect the
log_shipping_monitor_error_detail, sysjobhistory and
log_shipping_monitor_history_detail tables :(

Did you think about trying a date more recent than a month and a half ago?
(I don't think it is intended to affect the log_shipping tables, btw.)

On 9/16/08 10:13 AM, in article
AD1AE688-50E7-4A42-97F1-8A7AD68DC675@microsoft.com, "JeroenSchoen"
discussions.microsoft.com> wrote:

...and create a SQL Agent job that will do this like once a month to keep
the tables from growing

"Aaron Bertrand [SQL Server MVP]" wrote in message
news:C4F532AA.12572%%ten.xoc@dnartreb.noraa...

On 16 sep, 16:30, "Aaron Bertrand [SQL Server MVP]"
wrote:

On 17 sep, 10:06, Shuwi gmail.com> wrote:

I wonder the value of the "index" and "unused". Because ("reserved") =
("data") + ("index") + ("unused").

As the "data" in your situation is just "288.600KB" so what's this remaining
"5.689.752KB" about? Let's figure out this.

I'm somehow in doubt you have successfully shrinked the database.

--
Ekrem Önsoy

"Shuwi" gmail.com> wrote in message
news:be486581-6a1b-496a-a13a-461731e669eb@e39g2000hsf.googlegroups.com...

Hi Ekrem,
I agree, the dbcc shrinkfile did not work, my msdbdata.mdf is still 113 GB :(
There are 3 tables :
48230 MB --> log_shipping_monitor_error_detail
6106 MB --> log_shipping_monitor_history_detail

Those tables are not effected by sp_cleanup_log_shipping_history or
sp_purge_jobhistory !
So, the main question is.. how to get rid of 100 gig of error data...

when I try to run sp_purge_jobhistory to get rid o data in 47349 MB
-->sysjobhistory
my 8 procs machine all procs go 100%% .. so I cancelled it quick (this is
production ..)

"Ekrem Önsoy" wrote: