Unerwartet massives Wachstum von MSDB oder der Primary Filegroup einer SQL Server 2005 Datenbank
Wird das in SQL Server 2005 neu vorhandene Feature Service Broker verwendet kann es sein, dass folgenden Symptome auftauchen:
- MSDB wächst massiv an aber es werden keine Tabellen mit der Anzahl Rows ausgewiesen die der Filegrösse entsprechen würden. Die Datenbank Files lassen sich trotzdem nicht shrinken.
DBCC SHRINKFILE (msdbdata, 1) zeigt keine Wirkung
- wurden in einer Datenbank Service Broker Queues erstellt und diese korrekterweise in einer Data Filegroup erstellt wächst die Primary Filegroup trotzdem massiv an
Beispeil einer Queue Definition in eine User File Group:
CREATE QUEUE [dbo].[AuditQueue]
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[ReadAuditQueue] , MAX_QUEUE_READERS = 1 , EXECUTE AS N'dbo' )
ON [AuditDB01]
Das Primary Database File lässt sich auch hier nicht durch shrinken verkleinern.
Es ist dann zu prüfen, welche Queues vorhanden sind und wie diese konfiguriert sind. Insbesondere kann im SQL Server Errorlog überprüft werden, ob Hinweise auf ein nicht korrektes funktionieren der Queue bzw. dessen Abarbeitung vorhanden sind. Die Queue ist bei Fehlern zu stoppen bzw. zu entfernen und später, nach dem "Clean-Up" wieder neu aufzubauen.
Ist die Queue oder sind die Queues gestoppt so sollte auch das Wachstum der entsprechenden Datenbanken stoppen. Nun kann überprüft werden, ob in der sys.transmission_queue, der betroffenen Datenbank, oder der User Service Broker Queue, Einträge vorhanden sind die noch nicht verarbeitet wurden. Dies geschieht mit dem Statement:
SELECT COUNT(*) FROM sys.transmission_queue
oder am Beispiel einer User Queue mit dem Namen AuditQueue
SELECT COUNT(*) FROM AuditQueue Sind nun in der sys.transmission_queue Einträge vorhanden, muss entschieden werden ob diese noch gebraucht werden oder ob die Queue geleert werden kann. Meist wird der Service Broker dazu verwendet, dass Informationen möglichst zeitgleich verarbeitet werden. Somit wird es in den meisten Fällen so sein, dass die Queue geleert werden kann um ein Wiederherstellen der Service Broker Queues zu ermöglichen.
Die Queues können wie folgt geleert werden:
DECLARE @conversation uniqueidentifier
WHILE exists (SELECT 1 conversation_handle FROM sys.transmission_queue)
BEGIN
SET @conversation = (SELECT TOP 1 conversation_handle FROM sys.transmission_queue)
END conversation @conversation WITH CLEANUP
END
Dadurch werden die offenen Einträge beendet und die Daten aus den System Tabellen entfernt. Nach dem Durchlauf des Statements kann der Aufräumvorgang in den Systemtabellen noch weitere Zeit in Anspruch nehmen.
Sind dann alle Einträge entfernt, dann können auch die betroffenen Datenbank Files durch shrink verkleinert werden.
Empfehlung:
Bei der Verwendung von Service Broker Funktionen ist die Überwachung der betroffenen Datenbank Files unbedingt nötig.