Overview

Όλες οι databases που υπάρχουν μέσα σε ένα SQL Server instance χρειάζονται maintenance και tunning και δεν εξαιρούνται από αυτό οι system databases (master, model, msdb, distribution, ssisdb, tempdb). Είμαι σίγουρος ότι κάτι τέτοιο γίνεται για την tempdb, για τις άλλες έχω τις αμφιβολίες μου.

Μια από τις system databases είναι και η msdb και η οποία φυσικά και χρειάζεται maintenance και tunning. Δεν είναι λίγες οι φορές που έχω βρει την συγκεκριμένη database παντελώς παραμελημένη και αυτό εύκολα το μαρτυρά το μέγεθος της, το οποίο μπορεί να είναι μεγαλύτερο και από το σύνολο όλων των databases που υπάρχουν στο instance.

H msdb είναι μια database που χρησιμοποιείται από αρκετά services που υπάρχουν στο SQL Server όπως το SQL Server Agent service, Service Broker, Database Mail, Log Shipping κ.α., αλλά και από ενέργειες που γίνονται όπως τα backup και restores. Όλα αυτά καταγράφουν σε πίνακες που υπάρχουν μέσα στην msdb με αποτέλεσμα να μεγαλώνει το μέγεθος της άσκοπα καθώς δεν χρειάζεται να κρατάει όλες αυτές τις πληροφορίες. Για να πάρουμε όμως τα πράγματα με μία σειρά.

SQL Server Agent

Είναι γνωστό ότι με αυτό το service μπορούμε να έχουμε job scheduling το οποίο είναι φυσικά χρήσιμο καθώς εκτελούμε εργασίες που αφορούν την καθημερινότητα όπως backups, ενημερώσεις άλλων συστημάτων κ.α. Κάθε εκτέλεση μια τέτοιας εργασίας καταγράφεται μέσα στην msdb (dbo.sysjobhistory) με σκοπό να γνωρίζουμε αν έχει ολοκληρωθεί με επιτυχία ή όχι, όμως δεν έχει κανένα απολύτως νόημα να κρατάμε τις πληροφορίες αυτές για πάνω από μερικές μέρες. Τι νόημα έχει να μπορώ να δω το πως εκτελέστηκε μια τέτοια εργασία πριν 6 μήνες;

Βέβαια πρέπει να πω ότι η default συμπεριφορά του SQL Server είναι να κρατάει στο log 1000 rows όμως αυτό δεν είναι πρακτικό καθώς αν έχω αρκετά jobs πρακτικά έχω πληροφορίες που είναι για μία ή δύο μέρες, για αυτό και βγάζουμε τον περιορισμό αυτό. Από την στιγμή που κάνουμε αυτό θα πρέπει να καθαρίζουμε τo log σε τακτά χρονικά διαστήματα ώστε περιέχει μόνο αυτά που θέλουμε να κρατήσουμε.

Σε κάθε περίπτωση μπορούμε να φτιάξουμε ένα scheduled job το οποίο να διαγράφει αυτά που θεωρούμε ότι δεν χρειαζόμαστε πλέον. Αυτό μπορεί να γίνει με το παρακάτω τρόπο στον οποίο γίνεται χρήση της stored procedure που έχει η msdb για να διαγράφει το log των scheduled jobs και δεν είναι άλλη από την msdb.dbo.sp_purge_jobhistory με την οποία διαγράφω ότι είναι πάνω από 60 ημέρες παλαιό.

DECLARE @deletebefordate DATETIME = DATEADD(DAY,-60, GETDATE());
EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date = @deletebefordate;
GO

Database Mail

Από τα πράγματα που είναι απαραίτητα και κάνουν την ζωή μας ευκολότερη με το SQL Server είναι η χρήση του database mail με το οποίο κάνουμε τον SQL Server να μας μιλάει, καθώς μέσω των alerts & notifications μας ενημερώνει για τις αποτυχημένες εκτελέσεις jobs ή όταν έχει συμβεί κάποιο διακεκριμένο error alert.

Για κάθε email που μας στέλνει γράφει log entry σε διάφορα tables (sysmail_mailitems, sysmail_log, sysmail_attachments , sysmail_attachments_transfer) και δεν έχει νόημα να κρατάμε για πολύ την πληροφορία αυτή, εξάλλου ο SQL Server δεν είναι Exchange Server.

Για να διαγράψουμε αυτά που δεν θέλουμε αρκεί να καλέσουμε δύο stored procedures που σβήνουν όσα δεν χρειαζόμαστε από τα mails και τα logs, όπως στο παράδειγμα που σβήνω ότι είναι πάνω από 60 ημέρες παλαιό.

DECLARE @deletebefordate DATETIME = DATEADD(DAY,-60, GETDATE());
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @deletebefordate;
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @deletebefordate;
GO

Backup – Restore history

Από τα πράγματα που καθημερινά εκτελούνται σε ένα SQL Server instance είναι οι διαδικασίες backup, και μάλιστα αρκετές φορές μέσα στην ημέρα.

Όλα αυτά φυσικά και καταγράφονται σε tables στην msdb (backupfile, backupfilegroup, backupmediafamily, backupmediaset, backupset, restorefile, restorefilegroup, restorehistory)

Επειδή με τα backup όλοι έχουν μια ευαισθησία θεωρώ ότι δεν είναι φρόνιμο να γίνεται άμεσα η χρήση της stored procedure που διαγράφει την ιστορικότητα καθώς δεν θέλω να χαθούν κάποιες περιπτώσεις.

Για το λόγο αυτό όπως θα δείτε στο παρακάτω script επιλέγω να σβήσω αυτά που είναι παλαιότερα των 60 ημερών όμως ελέγχω το αν υπάρχει full backup που είναι παλαιότερο του διαστήματος αυτού για κάθε database και επιλέγω να σβήσω από την παλαιότερη ημερομηνία που μου επιστρέφεται.

DECLARE @deletebefordate DATETIME = DATEADD(DAY,-60, GETDATE());
WITH result AS 
(
    SELECT 
        bs.database_name
    ,    bs.backup_finish_date 
    ,    ROW_NUMBER() OVER (PARTITION BY bs.database_name ORDER BY bs.backup_finish_date DESC) AS rnk
    FROM    msdb.dbo.backupset AS bs
    WHERE 
        bs.type='d' 
        AND
        backup_finish_date < @deletebefordate
)
SELECT @deletebefordate = MIN(backup_finish_date)
FROM result 
WHERE rnk=1;

IF (@deletebefordate IS NOT NULL)
BEGIN
    EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @deletebefordate;
END
GO

Maintenance Plans

Αρκετοί χρησιμοποιούν τα maintenance plans και υλοποιούν τις εργασίες που αυτά προσφέρουν. Αν και προσωπικά δεν είμαι λάτρης τους καθώς έχω διαφωνίες σε κάποια πράγματα και τις οποίες έχω εκφράσει στο παρελθόν, θα πρέπει όσοι τα χρησιμοποιούν να σβήνουν τα logs που υπάρχουν για αυτά στους msdb.dbo.sysmaintplan_log, msdb.dbo.sysmaintplan_logdetail.

Αυτό μπορεί να γίνει πάλι με χρήση stored procedure που υπάρχει για αυτό το λόγο και είναι η msdb.dbo.sp_maintplan_delete_log.

DECLARE @deletebefordate DATETIME = DATEADD(DAY,-60, GETDATE());
EXEC msdb.dbo.sp_maintplan_delete_log @oldest_time = @deletebefordate;
GO

Log Shipping, DTA, Service Broker, Old SSIS versions

Εκτός από τα παραπάνω που είναι πράγματα που συναντάμε συχνά υπάρχουν και άλλα services που χρησιμοποιούν την msdb.

Οι περιπτώσεις αυτές όμως δεν έχουν κάποια stored procedure για να κάνουμε κάτι τέτοιο. Συνήθως σε αυτές τις περιπτώσεις δημιουργούμε scripts που είναι ραμμένα και κομμένα στο case που θέλουμε και για αυτό δεν προτείνω κάτι συγκεκριμένο.

Indexes & Statistics

Φυσικά και πρέπει να κάνουμε index & statistics maintenance στη msdb, όπως κάνουμε σε όλες τις άλλες databases. Είναι απαραίτητο!.

Ακόμα θέλω να τονίσω ότι αν δείτε ότι κάπου χρειάζεστε ένα νέο index, δημιουργήστε τον.

Summary

H msdb είναι μια σημαντική database και μπορεί να επηρεάσει αρνητικά το performance ενός SQL Server instance, για αυτό είναι σημαντικό να την κάνουμε monitor αλλά και να την συντηρούμε ώστε πρώτα απ’ όλα να μην ξεφύγει σε μέγεθος αλλά και για εκτελούνται όλα γρήγορα σε αυτή.