Hallo, wie kann man eine sehr große MSDB fixen / analisieren --> 4 herangehensweisen rausgesucht für Dich 

1. Die Daten löschen, danach einen Shrink ausführen:

USE MSDB
GO
DECLARE @varDate DATETIME
-- Set date to 30 days ago
SET @varDate = DATEADD(d,-30,GETDATE());
-- delete from sysmail_attachments
DELETE FROM dbo.sysmail_attachments
WHERE Last_mod_date < @varDate;
-- delete from sysmail_send_retries
DELETE FROM dbo.sysmail_send_retries
WHERE Last_send_attempt_date < @varDate;
-- delete from sysmail_allitems
EXEC Sysmail_delete_mailitems_sp
@Sent_before = @varDate;
-- delete from sysmail_log
EXEC Sysmail_delete_log_sp
@Logged_before = @varDate;
GO

Quelle: SQL Authority

2. Analyse und dann löschen, dann shrink:

SELECT object_name(i.object_id) as objectName,
i.[name] as indexName,
sum(a.total_pages) as totalPages,
sum(a.used_pages) as usedPages,
sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8) / 1024 as totalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as usedSpaceMB, 
(sum(a.data_pages) * 8) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY i.object_id, i.index_id, i.[name]
ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)
GO

ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];
ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];
truncate table msdb.dbo.sysmaintplan_logdetail;
truncate table msdb.dbo.sysmaintplan_log;
ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id])
REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);
ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id])
REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;

Quelle: DBAServices.com

3. Objektgröße ermitteln und Daten löschen mittels SP:

USE msdb
GO

SELECT TOP(10)
      o.[object_id]
    , obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.[type]
    , i.total_rows
    , i.total_size
FROM sys.objects o
JOIN (
    SELECT
          i.[object_id]
        , total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2))
        , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
    FROM sys.indexes i
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    WHERE i.is_disabled = 0
        AND i.is_hypothetical = 0
    GROUP BY i.[object_id]
) i ON o.[object_id] = i.[object_id]
WHERE o.[type] IN ('V', 'U', 'S')
ORDER BY i.total_size DESC

Quelle: blog.devart.com

4. Trim MSDB:

use msdb
/* Author : Kin
   Purpose: For dba.stackexchange.com - Trim down msdb
*/

-- Declaration
DECLARE @DeleteDate datetime 
DECLARE @DaysToRetain int
DECLARE @Batch int

set @DaysToRetain = 30
set @Batch = 5000
set @DeleteDate = convert(datetime,convert(varchar,getdate()-@DaysToRetain,101),101) 


-- ----------------
--  Index creation
-- ----------------
--  backupset
-- ----------------
Print 'Index Creation..'

if not exists (select * from msdb..sysindexes where name = 'IX_backupset_backup_set_uuid')
begin   
    Create NONCLUSTERED index IX_backupset_backup_set_uuid on backupset(backup_set_uuid)
end 

if not exists (select * from msdb..sysindexes where name = 'IX_backupset_media_set_id')
begin   
    Create NONCLUSTERED index IX_backupset_media_set_id on backupset(media_set_id)
end

if not exists (select * from msdb..sysindexes where name = 'IX_backupset_backup_finish_date')begin  
    Create NONCLUSTERED index IX_backupset_backup_finish_date on backupset(backup_finish_date)
end 

if not exists (select * from msdb..sysindexes where name = 'IX_backupset_backup_start_date')    
begin
    Create NONCLUSTERED index IX_backupset_backup_start_date on backupset(backup_start_date)
end


-- ------------
--  backupfile
-- ------------
if not exists (select * from msdb..sysindexes where name = 'IX_backupfile_backup_set_id')    
begin
    Create NONCLUSTERED index IX_backupfile_backup_set_id on backupfile(backup_set_id)
end

-- -------------------
--  backupmediafamily
-- -------------------
if not exists (select * from msdb..sysindexes where name = 'IX_backupmediafamily_media_set_id')    
begin
    Create NONCLUSTERED index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)
end

-- -------------------
--  backupfilegroup
-- -------------------
if not exists (select * from msdb..sysindexes where name = 'IX_backupfilegroup_backup_set_id')    
begin
    Create NONCLUSTERED index IX_backupfilegroup_backup_set_id on backupfilegroup(backup_set_id)
end

-- ----------------
--  restorehistory
-- ----------------
if not exists (select * from msdb..sysindexes where name = 'IX_restorehistory_restore_history_id')    
begin
    Create NONCLUSTERED index IX_restorehistory_restore_history_id on restorehistory(restore_history_id)
end

if not exists (select * from msdb..sysindexes where name = 'IX_restorehistory_backup_set_id')    
begin
    Create NONCLUSTERED index IX_restorehistory_backup_set_id on restorehistory(backup_set_id)
end

-- -------------
--  restorefile
-- -------------
if not exists (select * from msdb..sysindexes where name = 'IX_restorefile_restore_history_id')    
begin
    Create NONCLUSTERED index IX_restorefile_restore_history_id on restorefile(restore_history_id)
end

-- ------------------
--  restorefilegroup
-- ------------------
if not exists (select * from msdb..sysindexes where name = 'IX_restorefilegroup_restore_history_id')    
begin
    Create NONCLUSTERED index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id)
end

Print 'End of Index Creation..'


-- ------------------------------
--  Maintenance  before deletion
-- ------------------------------
--  Reindex 
-- ------------------------------

-- ----------------
--  backupset
-- ----------------
Print 'Maintenance Reindex..'

ALTER INDEX [IX_backupset_backup_set_uuid] ON [msdb].[dbo].[backupset] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupset_backup_set_uuid..'

ALTER INDEX [IX_backupset_media_set_id] ON [msdb].[dbo].[backupset] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupset_media_set_id..'


ALTER INDEX [IX_backupset_backup_finish_date] ON [msdb].[dbo].[backupset] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupset_backup_finish_date..'

ALTER INDEX [IX_backupset_backup_start_date] ON [msdb].[dbo].[backupset] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupset_backup_start_date..'


-- ------------
--  backupfile
-- ------------
ALTER INDEX [IX_backupfile_backup_set_id] ON [msdb].[dbo].[backupfile] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupfile_backup_set_id..'

-- -------------------
--  backupmediafamily
-- -------------------
ALTER INDEX [IX_backupmediafamily_media_set_id] ON [msdb].[dbo].[backupmediafamily] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupmediafamily_media_set_id..'

-- ------------------
--  backupfilegroup
-- ------------------
ALTER INDEX [IX_backupfilegroup_backup_set_id] ON [msdb].[dbo].[backupfilegroup] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print '[IX_backupfilegroup_backup_set_id]..'

-- ----------------
--  restorehistory
-- ----------------
ALTER INDEX [IX_restorehistory_restore_history_id] ON [msdb].[dbo].[restorehistory] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_restorehistory_restore_history_id..'


ALTER INDEX [IX_restorehistory_backup_set_id] ON [msdb].[dbo].[restorehistory] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_restorehistory_backup_set_id..'

-- -------------
--  restorefile
-- -------------
ALTER INDEX [IX_restorefile_restore_history_id] ON [msdb].[dbo].[restorefile] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_restorefile_restore_history_id..'


-- ------------------
--  restorefilegroup
-- ------------------
ALTER INDEX [IX_restorefilegroup_restore_history_id] ON [msdb].[dbo].[restorefilegroup] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_restorefilegroup_restore_history_id..'

Print 'End of Maintenance Reindex..'



--delete records    

print 'DEL restorefile..' 
if exists (select * from msdb.dbo.sysobjects where name = 'restorefile')    
begin 
    
    DELETE top (@Batch) FROM msdb..restorefile
    FROM msdb..restorefile rf with (nolock)
        INNER JOIN msdb..restorehistory rh with (nolock) ON rf.restore_history_id = rh.restore_history_id
        INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
    WHERE bs.backup_finish_date < @DeleteDate 
    
    
    while @@rowcount <> 0    
    begin    
    
        DELETE top (@Batch) FROM msdb..restorefile
        FROM msdb..restorefile rf with (nolock)
            INNER JOIN msdb..restorehistory rh with (nolock) ON rf.restore_history_id = rh.restore_history_id
            INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
        WHERE bs.backup_finish_date < @DeleteDate 
        
    end    
end    



print 'DEL restorefilegroup..'  
if exists (select * from msdb.dbo.sysobjects where name = 'restorefilegroup')    
begin 
    
    DELETE top (@Batch) FROM msdb..restorefilegroup
    FROM msdb..restorefilegroup rfg with (nolock)
        INNER JOIN msdb..restorehistory rh with (nolock) ON rfg.restore_history_id = rh.restore_history_id
        INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
    WHERE bs.backup_finish_date  < @DeleteDate 

    
    while @@rowcount <> 0    
    begin    
    
        DELETE top (@Batch) FROM msdb..restorefilegroup
        FROM msdb..restorefilegroup rfg with (nolock)
            INNER JOIN msdb..restorehistory rh with (nolock) ON rfg.restore_history_id = rh.restore_history_id
            INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
        WHERE bs.backup_finish_date  < @DeleteDate  
    End
End 
    
    
    
print 'Temp table ..'   
SELECT media_set_id, backup_finish_date
    INTO #Temp 
FROM msdb..backupset with (nolock)
WHERE backup_finish_date  < @DeleteDate 


    


print 'DEL backupfile..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupfile')    
begin 
    
    DELETE top (@Batch) FROM msdb..backupfile
    FROM msdb..backupfile bf with (nolock)
        INNER JOIN msdb..backupset bs with (nolock) ON bf.backup_set_id = bs.backup_set_id
        INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
    WHERE bs.backup_finish_date  < @DeleteDate 
    
    
    while @@rowcount <> 0    
    begin    
    
        DELETE top (@Batch) FROM msdb..backupfile
        FROM msdb..backupfile bf with (nolock)
            INNER JOIN msdb..backupset bs with (nolock) ON bf.backup_set_id = bs.backup_set_id
            INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
        WHERE bs.backup_finish_date  < @DeleteDate      
    End
End 



print 'DEL backupmediafamily..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupmediafamily')    
begin 
    
    DELETE top (@Batch) FROM msdb..backupmediafamily
    FROM msdb..backupmediafamily bmf with (nolock)
        INNER JOIN msdb..backupmediaset bms with (nolock) ON bmf.media_set_id = bms.media_set_id
        INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
    
    
    while @@rowcount <> 0    
    begin    
        
        DELETE top (@Batch) FROM msdb..backupmediafamily
        FROM msdb..backupmediafamily bmf with (nolock)
            INNER JOIN msdb..backupmediaset bms with (nolock) ON bmf.media_set_id = bms.media_set_id
            INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
    End
End 



print 'DEL backupfilegroup..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupfilegroup')    
begin 
    
    Delete top (@Batch) FROM msdb..backupfilegroup
        FROM msdb..backupfilegroup bfg with (nolock)
        INNER JOIN msdb..backupset bs with (nolock) ON bfg.backup_set_id = bs.backup_set_id
        INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
    
    
    while @@rowcount <> 0    
    begin    
    
        Delete top (@Batch) FROM msdb..backupfilegroup
        FROM msdb..backupfilegroup bfg with (nolock)
            INNER JOIN msdb..backupset bs with (nolock) ON bfg.backup_set_id = bs.backup_set_id
            INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id

    End
End 
        
    

print 'DEL restorehistory..' 
if exists (select * from msdb.dbo.sysobjects where name = 'restorehistory')    
begin       
    DELETE top (@Batch) FROM msdb..restorehistory
    FROM msdb..restorehistory rh with (nolock)
        INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
    WHERE bs.backup_finish_date  < @DeleteDate 
    
    
    while @@rowcount <> 0    
    begin    
    
        DELETE top (@Batch) FROM msdb..restorehistory
        FROM msdb..restorehistory rh with (nolock)
            INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
        WHERE bs.backup_finish_date  < @DeleteDate 
    
    End
End 
    
    
print 'DEL backupset..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupset')    
begin   

    DELETE top (@Batch) FROM msdb..backupset
    FROM msdb..backupset bs with (nolock)
        INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
        
    while @@rowcount <> 0    
    begin    
    
        DELETE top (@Batch) FROM msdb..backupset
        FROM msdb..backupset bs with (nolock)
            INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
    
    End
End 
    

print 'DEL backupmediaset..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupmediaset')    
begin   

    DELETE top (@Batch) FROM msdb..backupmediaset
    FROM msdb..backupmediaset bms with (nolock)
        INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
        
    while @@rowcount <> 0    
    begin    
    
        DELETE top (@Batch) FROM msdb..backupmediaset
        FROM msdb..backupmediaset bms with (nolock)
            INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
    
    End
End 
    

DROP TABLE #Temp



print 'Update Statistic on msdb'
-- Updates the statistics for all tables in the database. 
EXEC sp_updatestats
go

Quele: dba.stackexchange.com