sehr große msdb-DB

Question

Hallo Zusammen,

ich stehe aktuell vor dem Problem das unsere msdb mit einer Größe von 25 GB mdf und 40 GB ldf viel zu groß ist. Dabei ist mir rätselhaft wie es zu dieser Größe gekommen ist.
Es läuft täglich ein Wartungsplan mit 4 Tasks: Intigritätsprüfung, DB Verkleinern, Wartungs CleanUp und Sicherung. In meinen Augen nichts spektakuläres was diese Größe rechtfertigt.

Ich habe daraufhin mal die größten Tabellen in der msdb herrausgesucht:

tablename                   total_rows   total_table_size      data_size                 index_size             unused_size
backupfilegroup            13620        2008 KB                1864 KB                   32 KB                   112 KB
backupmediaset           13529        2664 KB                1536 KB                   864 KB                 264 KB
backupmediafamily       13529        3584 KB                2368 KB                   928 KB                  288 KB
backupset                    13620        8288 KB                7328 KB                   784 KB                  176 KB

Daraufhin habe ich mit sp_delete_backuphistory die Größen des einzelnen Tabellen erhablich verkleinern können:

tablename                                                                  total_rows         total_table_size    data_size         index_size    unused_size 

 

backupfilegroup	607	248,00	88,00	32,00	128,00
backupfile	1214	808,00	504,00	72,00	232,00
backupmediaset	607	1152,00	72,00	840,00	240,00
backupset	607	1312,00	320,00	784,00	208,00
backupmediafamily	607	1328,00	128,00	904,00	296,00

Leider hat sich an der gesamt Größe (mdf, ldf) noch immer nichts getan. Was kann ich noch tun?

Vielen Dank im Vorraus,

SA_LU

 

Answer 1

Hallo,

Wenn das mit gerade 8 MB Daten bereits die größten Tabellen sein sollen, ist das schon merkwürdig. Was liefert den

EXEC sp_spaceused;

für Gesamt-Werte für die msdb?

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Hallo Olaf,

hier das Ergebnis:

database_name     database_size      unallocated space
msdb                    63957.31 MB        5891.25 MB

reserved            data                 index_size         unused
18109120 KB     15348208 KB     556808 KB         2204104 KB

Answer 3

So ganz stimmig sind die Werte nicht gerade, es sind zwar 18 GB für Daten + Indizes reserviert (2 GB ungenutzt), das passt nicht ganz zu den 64 GB ( - 6G ungenutzt). Da solltest Du mal die Statistiken mit

DBCC UPDATEUSAGE(0);

aktualisieren; bei der Größe wird es aber etwas rappeln!!
Und danach noch mal sp_spaceused.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

Hallo Olaf,

danke für den Tipp. Das werde ich dann heute abend ausführen wenn die Kollegen weg sind. Gebe dann spätestes morgen das Ergebnis durch.

LG

Answer 5

Wie hattest Du "die größte Tabelle" ermittelt? Ich verwende das folgende Script dafür, falls Du es für weitere Analysen gebrauchen kannst:

SELECT SCH.name AS SchemaName,

       OBJ.name AS ObjName, OBJ.type_desc AS ObjType,

       INDX.name AS IndexName, INDX.type_desc AS IndexType,

       STAT.row_count AS StatRowCount,

       STAT.used_page_count * 8 AS UsedSizeKB,

       STAT.reserved_page_count * 8 AS RevervedSizeKB

FROM sys.dm_db_partition_stats AS STAT

     INNER JOIN sys.objects AS OBJ

         ON STAT.object_id = OBJ.object_id

     INNER JOIN sys.schemas AS SCH

         ON OBJ.schema_id = SCH.schema_id

     INNER JOIN sys.indexes AS INDX

         ON STAT.object_id = INDX.object_id

            AND STAT.index_id = INDX.index_id

ORDER BY STAT.reserved_page_count DESC,

         SCH.name, OBJ.name, INDX.name

 

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 6

Guten Morgen Olaf,

DBCC UPDATEUSAGE(0)dauerte geschlage 7,5 Stunden.

Ergebnis von sp_spaceused:

databse_name       database_size     unallocated Space
msdb                        39287.00 MB         6250.82 MB

reserved                     data                       index_size      unused
17740920 KB            11084848 KB      502016 KB     6154056 KB

Zur Ermittlung der Tabellengröße nutze ich ein anderes Script. ich habe aber auch mal deines getestet wobei ich zugeben muss das ich das Ergebnis noch nicht ganz verstanden habe. Hier mal das Ergebnis: (ich habe nur die ersten 10 Einträge genommen)

 

SchemaName	ObjName	ObjType	IndexName	IndexType	StatRowCount	UsedSizeKB	RevervedSizeKB
sys	queue_messages_2128726636	INTERNAL_TABLE	queue_clustered_index	CLUSTERED	4657944	10843176	16993200
sys	sysxmitqueue	SYSTEM_TABLE	clst	CLUSTERED	113602	382512	382832
sys	queue_messages_2128726636	INTERNAL_TABLE	queue_secondary_index	NONCLUSTERED	4657944	359880	361640
sys	sysobjvalues	SYSTEM_TABLE	clst	CLUSTERED	812	2184	2256
dbo	backupmediafamily	USER_TABLE	backupmediafamilyuuid	NONCLUSTERED	631	864	1056
dbo	backupmediaset	USER_TABLE	backupmediasetuuid	NONCLUSTERED	631	816	984
dbo	backupfile	USER_TABLE	PK__backupfile__7B264821	CLUSTERED	1262	600	808
sys	sysdercv	SYSTEM_TABLE	cl	CLUSTERED	1222	728	800
dbo	backupset	USER_TABLE	backupsetuuid	NONCLUSTERED	631	728	776
dbo	sysmaintplan_logdetail	USER_TABLE	NULL	HEAP	2309	648	728

 

Answer 7

Oha, das hat dann ja wirklich gerappelt, da waren die Statistiken wohl sehr out-of-sync. Wenn Du es jetzt erneut ausführen würdest, würde es erheblich schneller laufen, von daher vielleicht mal als Job alle paar Wochen laufen lassen.

Die Werte sehen ja nun viel schlüssiger aus und die Selektion zeigt auch, woher die Datenmengen kommen, aus der internen Tabelle queue_messages; die allein hat 17 GB.

Welche Queue es ist, kannst Du mit dem folgenden Statement ermitteln:

select IT.name AS TableName

      ,OBJ.name AS ParentName

from sys.internal_tables AS IT

     INNER JOIN

     sys.objects AS OBJ

         ON IT.parent_id = OBJ.object_id

 

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 8

Hallo Olaf,

ersteinmal Danke für deine Hilfe bis hierhin.

Leider komme ich nun auch nicht weiter. Mit Hilfe deines Scriptes habe ich nun herausgefunden das es die WMIEventProviderNotificationQueue ist. Google brachte mich da zu" Verwenden des WMI-Anbieters für Serverereignisse" (http://msdn.microsoft.com/de-de/library/ms186371.aspx).

Ich bin noch relativ unerfahren mit dem SQL Server. Ich weiß nun nicht ob es einen Weg gibt diese internen Tabellen ebenfalls zu verkleinern. Oder auch wie diese überhaupt so stark wachsen konnte. Ich habe mal einen Blick in diesen Service Brocker geworfen. Da sieht alles sehr nach Standard aus. Ich selber war da noch nie dran.

Fragen über Fragen :)

Vielen Dank

Answer 9

Hallo nochmal,

ich habe gerade folgendes gefunden:

1. Wiederherstellungsmodel von Vollständig auf einfach ändern
2. Logdatei verkleinern lassen
3. Wiederherstellungsmodel wieder auf Vollständig ändern

Wäre das auch hier eins sinnvolle Sache?

Answer 10

Normalerweise ist bei der msdb das Wiederherstellungsmodel im Standard bereits auf "Einfach"; wenn es bei Dir auf "Vollständig" steht, dann hat das jemand geändert und dann sollte man das Log auch sichern, damit es nicht stetig anwächst. Aber verkleinern könntest Du die, sie dürfte bei Dir ja ca. 20 GB groß sein.

Die WMIEventProviderNotificationQueue wird in der msdb angelegt, wenn z.B. Notifications oder Alerts angelegt werden, um bestimmte Events zu protokollieren. Das wird zum Beispiel von Monitoring Software genutzt, man kann sie auch für eigene Programme nutzen, um auf bestimmte Ereignisse zu reagieren. Welche Notifications es gibt, kannst Du so abfragen:

SELECT name, service_name, parent_class_desc

FROM msdb.sys.server_event_notifications;

 

SELECT name, event_source, database_name

FROM msdb.dbo.sysalerts;

 

Die Nachrichten der Notifications werden dann in die Queue eingefügt und das entsprechende Programm arbeitet die dann ab und normalerweise entfernt es diese anschließend. Hier wäre erst mal zu klären, wer den die Nachrichten eigentlich empfangen soll und wieso sie nicht entfernt werden.

Über die View transmission_queue könntest Du mal versuchen zu prüfen, ob da Hinweise zu finden sind:

 

SELECT TOP 20 *

FROM msdb.sys.transmission_queue

ORDER BY enqueue_time DESC

 

 Siehe auch Troubleshooting Routing and Message Delivery

 

 

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 11

Hallo Sa_Lu,

 

Realnamen wären schöner. Die msdb Speichert zu jedem Backup einen Datensatz in den von Dir beschriebenen Tabellen.
Wenn Du Transaktionslogs sicherst (am besten noch viele male am Tag) dann kommt da schon so einiges zusammen.

Du kannst hier ein wenig aufräumen. Schau dir die Tabelle backupset mal genauer an. Lass man mit Count(*) zählen
wieviel Sicherungen Du bereits gemacht hast. Du wirst dann staunen.

Mit der Funktion sp_delete_backuphistory kannst Du hier ordentlich aufräumen. Solltest Du die Einträge noch mal brauchen tut
es eine Sicherung vor dem Aufräumen auch noch.

http://msdn.microsoft.com/de-de/library/ms188328.aspx

Viele Grüße

Klaus

Answer 12

Hallo Sa_Lu,

 

Realnamen wären schöner. Die msdb Speichert zu jedem Backup einen Datensatz in den von Dir beschriebenen Tabellen.
Wenn Du Transaktionslogs sicherst (am besten noch viele male am Tag) dann kommt da schon so einiges zusammen.

Du kannst hier ein wenig aufräumen. Schau dir die Tabelle backupset mal genauer an. Lass man mit Count(*) zählen
wieviel Sicherungen Du bereits gemacht hast. Du wirst dann staunen.

Mit der Funktion sp_delete_backuphistory kannst Du hier ordentlich aufräumen. Solltest Du die Einträge noch mal brauchen tut
es eine Sicherung vor dem Aufräumen auch noch.

http://msdn.microsoft.com/de-de/library/ms188328.aspx

Viele Grüße

Klaus

Hallo Klaus,

bei allem Respekt - der Realname ist so unwichtig wie ein Kropf im Hals.
Hättest Du stattdessen mal den Eingangsthread vollständig gelesen, dann hättest Du gelesen, dass ein sp_delete_backuphistory bereits gelaufen ist!
Ausserdem hat Sa_Lu und Olaf auch schon festgestellt, dass eine ganz andere Relation die Ursache ist...

 

---

Uwe Ricken

MCIT Database Administrator 2005
MCIT Database Administrator 2008
MCTS SQL Server 2005
MCTS SQL Server 2008, Implementation and Maintenance
db Berater GmbH
http://www-db-berater.de

Answer 13

Hallo Olaf,

viele Dank für deine Hilfe. ich habe die Datenbank nun auf unter 1 GB runterbekommen. Super! Deine Antworten haben sehr geholfen und auch mich weitergebracht. Danke vielmals.

LG

SA_LU