go backarticles

Articles of SQLschool.gr Team

MSDB database needs maintenance and tunning. Don’t forget that.

Antonios Chatzipavlis

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 ημέρες παλαιό.

Delete jobs history

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 ημέρες παλαιό.

Delete Database Mail items and logs

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 και επιλέγω να σβήσω από την παλαιότερη ημερομηνία που μου επιστρέφεται.

Delete Backup-restore history

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.

Delete Maintenance Plans logs

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 αλλά και να την συντηρούμε ώστε πρώτα απ’ όλα να μην ξεφύγει σε μέγεθος αλλά και για εκτελούνται όλα γρήγορα σε αυτή.


Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.



Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in

About us   Contact us   Terms of Use   Privacy   Register
sql school greece logo
© 2010-2021 All rights reserved

This site uses cookies for operational and analytics purposes only.
By continuing to browse this site, you agree to their use.