SQL Server MSDB Database Tables

Di: Eric Blinn
Panoramica

Ci sono molti oggetti che compongono il database MSDB ed essere in grado di interrogare direttamente alcune di queste tabelle può essere molto utile. Vedremo alcuni dei thetables che sono utili per interrogare.

Quali sono le tabelle chiave nel database MSDB che sono importanti per SQL ServerProfessionals conoscere?

Ci sono un gran numero di tabelle in MSDB che un SQL Server professional shouldknow. Questo autore ha scritto alcuni MSSQLTips specificamente su di loro. Questo suggerimento riguarda le tabelle di SQL Server Agent equesto suggerimento copre le tabelle di backup e ripristino della cronologia.

Questo tutorial metterà in evidenza alcuni gruppi di tabelle MSDB che vengono comunemente interrogati.

SELECT * FROM dbo.suspect_pages;

Suspect_pages aiuta a tenere traccia delle pagine di dati corrotti o quelli con il potenziale forcorruption a causa di un sottosistema disco guasto. Queste potrebbero essere pagine che sono state trovate a causa della normale interrogazione che ha avuto un errore 823 o 824, sono state trovate da un comando DBCCCheck o sono state trovate durante una convalida del checksum di backup. I DBA dovrebbero essere consapevoli di questa tabella e interrogarla occasionalmente per essere sicuri che non ci siano errori non corretti.

Esiste un limite di 1000 righe per questa tabella. Speriamo che non è un numberthat è comunemente testato, ma se è più vecchio righe non sono invecchiati fuori automaticallyto fare spazio per quelli nuovi. SQL Server interrompe semplicemente la registrazione delle informazioni seci sono 1000 righe nella tabella. Per questo motivo, le righe più vecchie dovrebbero essere cancellateda un DBA dopo aver esaminato e risolto ciò che le ha causate. Per saperne di più su thistable inthis suggerimento!

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;

La tabella backupset contiene una riga ogni volta che viene eseguito un backup. tablebackupmediafamily contiene una riga per ogni file di backup creato o aggiunto.La maggior parte dei DBA esegue il backup di un database per file e lascia queste tabelle con una relazione 1: 1.Se si utilizzano backup a strisce o si inseriscono più backup in un singolo file, non saranno 1:1.

Considera questo set di risultati dalla query precedente da un backup a strisce.

dettagli di backup

Ci sono 2 righe, ma solo un singolo ID set di backup, numero 1. Questo indicac’è solo un backup. Il backup è stato inviato allo stesso id media set, 1, ma quel set contiene 2 numeri di sequenza. Ciò significa che per completare un restoreof il database testtde entrambi i file saranno necessari e devono essere elencati.

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';

Ora considera questo set di risultati per una serie di backup che condividono un singolo file.

dettagli di backup

Esistono 2 set di backup distinti, 2 e 3, ma un singolo media set e un nome di file singolo. Questo indica che ci sono più backup che condividono un singlefile. Entrambe le righe hanno una sequenza di 1 che indica che non vi è alcuna striscia per entrambi i backup. In questo caso, la colonna posizione diventa importante. “FILE” 1 sarà un altro backup testtde mentre “FILE” 2 sarà un backup 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 ..;

Questa tabella registra tutti gli eventi correlati a dbmail. È la stessa informazione che può essere trovata in SSMS facendo clic sul menu contestuale” Visualizza registro posta del database”.

 Questa schermata mostra l'opzione del menu contestuale precedentemente menzionata in Management-Database Mail-View Database Mail Log
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;

Questo gruppo di tabelle registra le impostazioni DBMailprofile e account. La tabella sysmail_profile contiene una riga perprofile mentre sysmail_account contiene una riga per ogni account. Ogni riga nella tabella sysmail_profileaccount corrisponde da 1 profilo a 1 account. Ciò consente una relazione molti-a-molti tra profili e account.

Questo output di esempio mostra 2 profili che condividono un singolo account.

 Questa schermata mostra 2 profili numerati 1 e 2 che condividono un singolo account con l'ID account di 1.

Posso vedere il codice per le tabelle e le viste di sistema in MSDB e, in caso affermativo, come?

Le tabelle di sistema in MSDB possono essere visualizzate utilizzando sp_help o utilizzando l’opzione contextmenu “Design” in Esplora oggetti.

exec sp_help sysjobs;
Questa schermata mostra l'output del precedente comando TSQL. Mostra i dettagli della tabella sysjobs da MSDB.

Le viste non hanno un’opzione modifica o “script come” nel loro menu contestuale come fanno userviews, ma possono ancora essere visualizzate usando sp_helptext.

exec sp_helptext sysjobs_view;
Questa schermata mostra l

Posso modificare questi oggetti nel database MSDB?

SQL Server consentirà di modificare le tabelle e le viste di sistema, ma ciò non dovrebbe assolutamente essere fatto. Le procedure guidate, gli schermi SSMS e gli schermi di Azure Data Studio prevedono che questi oggetti si comportino in un certo modo e la loro modifica potrebbe causare il fallimento di thoseto.

Potrebbe essere fatta un’eccezione per gli indici non cluster. Su un’istanza con molti database e un sacco di cronologia di backup o molti lavori di SQL Server Agent con una grande quantità di cronologia, alcune query di monitoraggio possono finire per funzionare in modo proibitivo e l’aggiunta di indici non cluster per supportare la segnalazione o altre operazioni può risolvere questo problema.

Posso memorizzare i miei oggetti nel database MSDB?

SQL Server consentirà di aggiungere oggetti al database MSDB, ma ciò non dovrebbe essere consentito. Invece, mantenere un database utente speciale con un namelike “_DBA” in cui gli amministratori possono mantenere gli oggetti che vengono utilizzati per gestire l’istanza.

Ultimo aggiornamento: 31/3/2020

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.