Tablas de base de datos MSDB de SQL Server

By: Eric Blinn
Overview

Hay muchos objetos que componen la base de datos MSDB y poder consultar algunas de estas tablas directamente puede ser muy útil. Veremos algunas de las tablas que son útiles para consultar.

¿Cuáles son las tablas clave de la base de datos MSDB que son importantes para que los profesionales de servidores SQL conozcan?

Hay un gran número de tablas en MSDB que un profesional de SQL Server debería conocer. Este autor ha escrito algunos MSSQLTips específicamente sobre ellos. Este consejo trata sobre las tablas del agente de SQL Server y este consejo cubre las tablas del historial de copia de seguridad y restauración.

Este tutorial resaltará algunos grupos de tablas MSDB que se consultan comúnmente.

SELECT * FROM dbo.suspect_pages;

Suspect_pages ayuda a rastrear páginas de datos corruptas o con la posible corrupción debido a un subsistema de disco defectuoso. Estas podrían ser páginas que se encontraron debido a consultas normales que tenían un error 823 u 824, fueron encontradas por un comando DBCCCheck, o se encontraron durante una validación de suma de comprobación de copia de seguridad. Los DBA deben ser conscientes de esta tabla y consultarla ocasionalmente para asegurarse de que no haya errores sin corregir.

Hay un límite de 1000 filas para esta tabla. Esperemos que no sea un número que se pruebe comúnmente, pero si es más antiguo, las filas no se envejecen automáticamente para hacer espacio para otras nuevas. SQL Server simplemente detiene la grabación de información si hay 1000 filas en la tabla. Debido a esto, las filas más antiguas deben eliminarse mediante un DBA después de revisar y resolver lo que las causó. Lea más sobre este producto en este consejo!

SELECT backup_set_id, database_name, bset.media_set_id, position, family_sequence_number, physical_device_nameFROM msdb.dbo.backupset bset INNER JOIN backupmediafamily bfam ON bset.media_set_id = bfam.media_set_id;

El conjunto de copias de seguridad de la tabla contiene una fila cada vez que se realiza una copia de seguridad. La familia tablebackupmediaf contiene una fila por cada archivo de copia de seguridad creado o anexado.La mayoría de los DBA hacen una copia de seguridad de una base de datos por archivo y dejan estas tablas con una relación 1:1.Si se usan copias de seguridad a rayas o se rellenan varias copias de seguridad en un solo archivo, entonces no serán 1:1.

Considere este conjunto de resultados de la consulta anterior de una copia de seguridad rayada.

 detalles de copia de seguridad

Hay 2 filas, pero solo un id de conjunto de copia de seguridad único, número 1. Esto indica que solo hay una copia de seguridad. La copia de seguridad se envió al mismo id de conjunto de medios, 1, pero ese conjunto contiene 2 números de secuencia. Esto significa que para completar un restore de la base de datos testtde se necesitarán ambos archivos y se deben enumerar.

RESTORE HEADERONLY FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Stripe1.bak',DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Stripe2.bak';

Ahora considere este conjunto de resultados para una serie de copias de seguridad que comparten un solo archivo.

 detalles de copia de seguridad

Hay 2 conjuntos de copia de seguridad distintos, 2 y 3, pero un solo conjunto de medios y un nombre de archivo único. Esto indica que hay varias copias de seguridad que comparten un solo archivo. Ambas filas tienen una secuencia de 1 que indica que no hay tiras para ninguna copia de seguridad. En este caso, la columna de posición se vuelve importante. «FILE» 1 será otra copia de seguridad testtde, mientras que «FILE» 2 será una copia de seguridad MSDB.

RESTORE HEADERONLY FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\MultiBackupFile.bak'WITHH FILE = 1;RESTORE HEADERONLY FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\MultiBackupFile.bak'WITH FILE = 2;

SELECT * FROM ..;

Esta tabla registra todos los dbmail eventos relacionados. Es la misma información que se puede encontrar en los SSM haciendo clic en la opción de menú contextual» Ver registro de correo de la base de datos».

 Esta captura de pantalla muestra la opción de menú contextual mencionada anteriormente en Administración-Correo de base de datos-Ver registro de correo de base de datos
SELECT sp.profile_id, sp.name, spa.account_id, sa.name, sa.email_address, sa.display_name, ISNULL(spp.is_default, 0) IsDefaultProfileFROM . sp INNER JOIN . spa ON sp.profile_id = spa.profile_id INNER JOIN . sa ON spa.account_id = sa.account_id LEFT OUTER JOIN . spp ON sp.profile_id = spp.profile_id;

Este grupo de tablas registra la configuración del archivo DBMAIL y de la cuenta. La tabla sysmail_profile contiene una fila por fichero, mientras que sysmail_account contiene una fila para cada cuenta. Cada fila de la tabla sysmail_profileaccount coincide con 1 perfil a 1 cuenta. Esto permite una relación de muchos a muchos entre perfiles y cuentas.

Esta salida de ejemplo muestra 2 perfiles que comparten una sola cuenta.

Esta captura de pantalla muestra 2 perfiles numerados 1 y 2 que comparten una sola cuenta con el id de cuenta de 1.

¿Puedo ver el código de las tablas y vistas del sistema en MSDB y, de ser así, cómo?

Las tablas del sistema en MSDB se pueden ver usando sp_help o usando la opción» Design » contextmenu en el Explorador de objetos.

exec sp_help sysjobs;
Esta captura de pantalla muestra la salida del comando TSQL anterior. Muestra los detalles de la tabla sysjobs de MSDB.

Las vistas no tienen una opción modificar o «script como» en su menú contextual como lo hacen las vistas de usuario, pero aún se pueden ver usando sp_helptext.

exec sp_helptext sysjobs_view;
Esta captura de pantalla muestra la salida del código TSQL anterior. Es una instrucción "CREATE VIEW" que muestra la definición de la vista.

¿Puedo cambiar estos objetos en la base de datos MSDB?

SQL Server permitirá cambiar las tablas y vistas del sistema, pero esto no debe hacerse de forma absoluta. Los asistentes, las pantallas de SSMS y las pantallas de Azure Data Studio esperan que estos objetos se comporten de cierta manera y cambiarlos podría provocar que fallen.

Se puede hacer una excepción para los índices no agrupados. En una instancia con muchas bases de datos y un montón de historial de copias de seguridad o muchos trabajos de agentes de SQL Server con una gran cantidad de historial, algunas consultas de supervisión pueden terminar ejecutándose con una lentitud prohibitiva y agregar índices no agrupados para admitir informes u otras operaciones puede resolver este problema.

¿Puedo almacenar mis propios objetos en la base de datos MSDB?

SQL Server permitirá agregar objetos a la base de datos MSDB, pero probablemente no se permita. En su lugar, mantenga una base de datos de usuarios especial con un nombre «_DBA» donde los administradores pueden guardar los objetos que se utilizan para administrar la instancia.

Última actualización: 31/3/2020

Deja una respuesta

Tu dirección de correo electrónico no será publicada.