Cómo reducir el tamaño de MSDB de 42 GB a 200 MB
Recientemente tuve un minuto libre para averiguar por qué el antiguo servidor de prueba funcionaba demasiado lento... No tuve nada que ver con eso, pero realmente quería saber qué estaba mal con el servidor.
Primero, abrí el Monitor de recursos y miré la carga total. El sqlserv.exe el proceso tomó 100% fuera UPC y genera una gran cola de discos de más de 300... mientras que un número superior a 1 se considera problemático.
Al analizar la actividad del disco, observé continuidad IO operaciones en msdb:
D:SQL_2012SYSTEMMSDBData.mdf D:SQL_2012SYSTEMMSDBLog.ldf
Miré el tamaño msdb:
SELECT name, size = size * 8. / 1024, space_used = FILEPROPERTY(name, 'SpaceUsed') * 8. / 1024 FROM sys.database_files
y cambie al modo "facepalm":
name size space_used ------------ -------------- --------------- MSDBData 42626.000000 42410.374395 MSDBLog 459.125000 6.859375
El archivo de datos es de 42 GB ... Después de un breve descanso, comencé a investigar el motivo de un tamaño tan "poco saludable" msdby cómo superar los problemas de rendimiento del servidor.
Revisé las solicitudes del costo de los recursos que se ejecutan en el servidor:
SELECT
r.session_id
, db = DB_NAME(r.database_id)
, r.[status]
, p.[text]
--, sql_text = SUBSTRING(p.[text], (r.statement_start_offset / 2) + 1,
-- CASE WHEN r.statement_end_offset = -1
-- THEN 2147483647
-- ELSE ((r.statement_end_offset - r.statement_start_offset) / 2) + 1
-- END)
, r.cpu_time
, r.total_elapsed_time
, r.reads
, r.writes
, r.logical_reads
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) p
WHERE r.[sql_handle] IS NOT NULL
AND r.session_id != @@SPID
ORDER BY logical_reads DESC
En primer lugar está el procedimiento almacenado del sistema:
session_id db status text cpu_time total_elapsed_time reads writes logical_reads ---------- -------- -------- ------------------------------------- ----------- ------------------ ------- --------- --------------- 62 msdb running create procedure [sys].[sp_cdc_scan] 111638 6739344 618232 554324 2857923422
quiero decir Centro de Control de Enfermedades (Cambiar la recopilación de datos), que se utiliza como una herramienta para capturar cambios en los datos. Centro de Control de Enfermedades basado en la lectura del registro de transacciones y siempre funciona de forma asíncrona usando corredor de servicios.
al enviar Notificación de eventos oficina corredor de servicios, es posible que el mensaje no llegue a su destino debido a problemas de configuración y luego se archiva en una tabla separada. En general, si corredor de servicios de uso frecuente, es necesario controlar la condición sys.sysxmitqueue. Si la cantidad de datos en la tabla aumenta constantemente, es un error o estamos usando corredor de servicios incorrecto.
Plan de ejecución desde Estudio DbForge:
Esta consulta devuelve la lista superior de objetos y su tamaño:
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
Después de completar la consulta, recibí los siguientes resultados:
object_id obj type total_rows total_size ----------- -------------------------------- ---- ------------ ----------- 68 sys.sysxmitqueue S 6543502968 37188.90 942626401 dbo.sysmail_attachments U 70 2566.00 1262627541 dbo.sysmail_attachments_transfer U 35 2131.01 1102626971 dbo.sysmail_log U 44652 180.35 670625432 dbo.sysmail_mailitems U 19231 123.39 965578478 dbo.sysjobhistory U 21055 69.05 366624349 dbo.backupfile U 6529 14.09 727673640 dbo.sysssispackages U 9 2.98 206623779 dbo.backupset U 518 1.88 286624064 dbo.backupfilegroup U 3011 1.84
Debo decir que todas las tablas de esta lista no las vamos a ignorar. Pero primero tenemos que resolver el problema con sys.sysxmitqueue.
No podemos eliminar datos directamente de sys.sysxmitqueueporque esta tabla es un objeto del sistema (S). Después de buscar un poco, encontré una manera de llegar allí. servidor SQL para quitar de la mesa. Al crear uno nuevo corredor de serviciosse eliminarán todos los mensajes relacionados con el intermediario anterior.
USE msdb GO ALTER Base de datos msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE
Pero antes de ejecutar el comando, se recomienda encarecidamente desactivar Agente de servidor SQL y cambiar servidor SQL oficina Modo de usuario único. Me tomó alrededor de 10 minutos eliminar los mensajes existentes en todas las colas corredor de servicios. Al finalizar, recibí el siguiente mensaje:
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Después de reiniciar servidor SQL el servicio todos los problemas de desempeño desaparecieron... mi corazón se llenó de alegría y pudimos ponerle fin. Pero recuerda que esta no fue la única mesa grande msdb. Veamos el resto…
Aquellos a quienes les gusta enviar correo a través de base de datos de correo debería saber esto servidor SQL registros y mantener todo el boletín msdb. Todos los archivos adjuntos de correo electrónico que se envían junto con el texto de la carta se almacenan cuidadosamente allí ... Por lo tanto, se recomienda eliminar esta información con regularidad. Esto se puede hacer con sus propias manos, es decir, preste atención a las tablas que deben eliminarse:
SELECT o.name, p.[rows]
FROM msdb.sys.objects o
JOIN msdb.sys.partitions p ON o.[object_id] = p.[object_id]
WHERE o.name LIKE 'sysmail%'
AND o.[type] = 'U'
AND p.[rows] > 0
Alternativamente, use procedimientos almacenados listos para usar sysmail_delete_mailitems_sp y sysmail_delete_log_sp:
DECLARE @DateBefore DATETIME SET @DateBefore = DATEADD(DAY, -7, GETDATE()) EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DateBefore --, @sent_status="sent" EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @DateBefore
Historia de Agente de servidor SQL Las tareas también se almacenan en msdb. Si hay demasiadas entradas en el diario, es difícil trabajar con ellas, así que trato de limpiarlo regularmente con sp_purge_jobhistory:
DECLARE @DateBefore DATETIME SET @DateBefore = DATEADD(DAY, -7, GETDATE()) EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @DateBefore
También debo mencionar la información de respaldo incluida en el sistema. msdb. Las entradas de copia de seguridad antiguas se pueden eliminar usando sp_delete_backuphistory:
DECLARE @DateBefore DATETIME SET @DateBefore = DATEADD(DAY, -120, GETDATE()) EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @DateBefore
Pero tenga en cuenta una cosa: cuando elimina una base de datos, la información sobre su copia de seguridad permanece en ella. msdb:
USE [master]
GO
IF DB_ID('backup_test') IS NOT NULL BEGIN
ALTER Base de datos [backup_test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP Base de datos [backup_test]
END
GO
CREATE Base de datos [backup_test]
GO
BACKUP Base de datos [backup_test] TO DISK = N'backup_test.bak'
GO
DROP Base de datos [backup_test]
GO
SELECT *
FROM msdb.dbo.backupset
WHERE database_name="backup_test"
En mi caso, si las bases de datos se crean y eliminan con frecuencia, esto puede conducir a la ampliación msdb. En una situación en la que la información de la copia de seguridad no sirve, se puede eliminar usando un archivo guardado. sp_delete_database_backuphistory:
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'backup_test'
Resumen
Sistema de bases de datos msdb utilizado por muchos componentes servidor SQLtal como corredor de servicios, Agente de servidor SQLy base de datos de correo. Cabe señalar que no existe un plan de servicio listo para usar que tenga en cuenta lo anterior, por lo que es muy importante tomar medidas preventivas regulares. En mi caso, después de borrar la información innecesaria y reducir el archivo, el tamaño msdb se convirtió en 200 MB frente a los 42 GB originales.
Espero que esta publicación sea una historia instructiva sobre los beneficios de la administración continua de las bases de datos de los usuarios y del sistema.
Artículos de interés