Help! My Transaction Log Keep Growing!
השבוע עמית לעבודה סיפר לי שלאחד מסד הנתונים שלהם בשרת גדל הלוג עד ש"חנק" את הכונן.
כאשר הוא בא לטפל בנושא הטיפול המיידי שלו היה לשנות את הRecovery Model מFULL לSimple וביצוע הסקריפט הבא –
USE [master]
GO
ALTER DATABASE [Demo] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [Demo]
GO
CHECKPOINT;
CHECKPOINT;
CHECKPOINT;
CHECKPOINT;
CHECKPOINT;
CHECKPOINT;
DBCC SHRINKFILE (N'Demo_log' , 0)
GO
לאחר מכן החזיר את הRecovery Model של מסד הנתונים ל FULL ביצע גיבוי ונתן לגיבויי הלוג להמשיך.
ׁׁׁׁׁׁׁׁׁׁׁׁׁׁׁׁׁׁPaul Randal (B|T) כתב מאמר על ניהול נכון של קובץ הלוג עוד ב2009 Importance of proper transaction log size management
על רגל אחת הוא מתאר את הפעולה כך –
Switch to SIMPLE when it fills up, shrink the log, then switch back to FULL. This is worse than 2, 3, or 5. If you shrink the log, then it's going to grow again – possibly causing VLF fragmentation (see Transaction Log VLFs – too many or too few?), and definitely causing your workload to pause while the log grows, as the log can't use instant initialization (see Search Engine Q&A #24: Why can't the transaction log use instant initialization?).
פול מדבר על פתרון כשגרה לאירועים המתוארים במאמר, ופה היה מקרה נקודתי.
אך המקרה גרם לי לחשוב על פתרון שיוכל לעזור בניטור הבעיה ויתקוף אותה ממספר מקומות שונים.
אז מה הבעיה בעצם בפתרון של העמית –
1. זיהוי גדילת לוג בשלב מאוחר.
2. טיפול מעמיק בבעיה.
הגדרת הבעיה שלנו מחדש – גדילה רצינית של קובץ הלוג.
הקדמה לפתרון:
תחילה נזהה את הסיבות האפשריות לגדילת הלוג.
1. בחירה לא נכונה של Recovery Models
בחירה ב- Full Recovery Mode ולקיחת גיבויי לוג לעיתים רחוקות מידי או לא לקחת גיבויים בכלל(ההנחה היא שקרתה תקלה ולא שלא בוצע בכלל גיבויי לוג)
תוכל לקרוא בפירוט על ה Recovery Models השונים - כאן
2. סיבות נוספות שאת כולן ניתן לראות בעמודת log_reuse_wait /log_reuse_wait_desc מתוך – sys.databases
0 = Nothing - What it sounds like.. Shouldn't be waiting
1 = Checkpoint - Waiting for a checkpoint to occur. This should happen and you should be fine - but there are some cases to look for here for later answers or edits.
2 = Log backup - You are waiting for a log backup to occur. Either you have them scheduled and it will happen soon, or you have the first problem described here and you now know how to fix it
3 = Active backup or restore - A backup or restore operation is running on the database
4 = Active transaction - * There is an active transaction that needs to complete (either way - ROLLBACK or COMMIT) before the log can be backed up. This is the second reason described in this answer.
5 = Database mirroring Either a mirror is getting behind or under some latency in a high performance mirroring situation or mirroring is paused for some reason
6 = Replication - There can be issues with replication that would cause this - like a log reader agent not running, a database thinking it is marked for replication that no longer is and various other reasons. You can also see this reason and it is perfectly normal because you are looking at just the right time, just as transactions are being consumed by the log reader
7 = Database snapshot creation You are creating a database snapshot, you'll see this if you look at just the right moment as a snapshot is being created
8 = Log Scan I have yet to encounter an issue with this running along forever. If you look long enough and frequently enough you can see this happen, but it shouldn't be a cause of excessive transaction log growth, that I've seen.
9 = An AlwaysOn Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database. About the clearest description yet..
אני רוצה להתרכז בטרנזקציות ארוכות(log_reuse_wait_desc = Active transaction).
למשל, כאשר אנו מבצעים מחיקה של כמות שורות מאוד גדולה כפעולה לילית.
ננסה לתאר תרחיש אפשרי, אנו באים בבוקר ורואים שהלוג צמח לממדים גדולים.
יכול להיות שתחילה נרצה לצמצם את הלוג, אך איך פעולה זאת היא רק נקודתית וסביר להניח שהלוג יגדל שוב.
הפתרון שאני מציע:
ניטור (פרואקטיב) - שימוש בהתראות שיקפיצו לנו איזה טרנזקציה ספציפית היא זו שגרמה ללוג לגדול.
ניטור (פרואקטיב) - סף גודל הכונן. (בהנחה שהשתמשנו בBest Practice והפרדנו את קבצי הלוג לכונן נפרד.)
ניטור (פרואקטיב) - סף התפוסה (באחוזים) של קובץ הלוג.(מטרה לנטר אם לא בוצעו גיבויים בטווח מתאים.)
זיהוי טרנזקציות גדולות במסד הנתונים.( כפתרון לטווח הארוך.)
קצת על סוגי ניטור:
פסיבי – שיטת בת היענה. המשמעות היא דחיפת ראשך בחול ולהתעלם מבעיות. - עובד בחלק מהזמן :)
אקטיבי – ניטור ע"פ זמנים או דרישה – זיהוי הבעיות וטיפולם(לרב משתלם אך לעיתים הזיהוי הוא מאוחר מידי)
פרו- אקטיבי – הגדרת ספים להוצאת התראות כאשר יש זמן לבצע תיקונים לטובת טיפול בבעיה לפני שהיא גדלה – פה תמיד נרצה להיות.
הניטור בפתרון מבוסס על שליחת מייל. ההנחה שהמייל מחובר למכשיר סלולארי וכך אם יש תקלה מהותית תוכל/י לדעת זאת בזמן אמת.
הפתרון נבדק בשרתי 2012/2014
חבילה 1 - ניטור פרואקטיבי – שימוש בAlerts שיקפיצו לנו איזה טרנזקציה ספציפית היא זו שגרמה ללוג לגדול.
* לחבילה נדרש שיהיה שרת מייל מוגדר לשרת הSQL – ניתן לקרוא בהרחבה כאן
החבילה מורכבת מ3 פרוצדורות:
Log.usp_Monitor_SetAlertLogGrowth – מקבלת שם מסד נתונים, ויוצרת התראה ע"פ גודל קובץ הלוג במצב הנוכחי.
Log.usp_Monitor_GetLogGrowth – אוספת פרטים ע"ג הפעולה שבוצעה וגרמה ללוג לגדול.
מפעילה 2 פרוצדורות :
Log.usp_Monitor_SetAlertLogGrowth – לטובת קביעת הסף מחדש.
Log.usp_Monitor_LogGrowthNotifier – לטובת שליחת התראה.
Log.usp_Monitor_LogGrowthNotifier – שליחת כל הטרנזקציות/פעולות שגרמו ללוג לגדול אם הלוג גדל מעל הסף שקבענו.
טבלאות-
[_Admin_].[General_Configuration] - טבלה לקביעת הסף בהתייחס למסד נתונים.
Job
_Admin_:: MonitorLogGrowth_SetBaseLine – הJob מופעל ע"י ההתראה
כאשר זיהינו את הטרנזקציות הבעייתיות, נותר לנו לראות איזה טיפול נבחר כדי לטפל בכל מקרה לגופו.
ניתן להוריד את הקוד מכאן
חבילה 2 - ניטור פרואקטיבי של סף גודל הכונן.
* בהנחה שהשתמשנו בBest Practice והפרדנו את קבצי הלוג לכונן נפרד.
* לחבילה נדרש אופרטור מוגדר לשרת הSQL – ניתן לקרוא בהרחבה כאן
החבילה מורכבת מפרוצדורה אחת:
[_Admin_].[usp_SetVolumeAlert] – הפרוצדורה מגדירה Alert ע"פ הכוננים שהגדרנו בטבלה – [_Admin_].[DriveAlert] ולהפעיל אותה פעם אחת על השרת.
הפרוצדורה מגדירה התראה ומקשרת אותה לאופרטור כל פעם שיש חריגה מהסף שקבענו.
טבלה
[_Admin_].[DriveAlert] – הטבלה מחזיקה את הגדרות הכוננים שאנו רוצים לנטר בשרת.
Job
_Admin_ :: SetLowDiskSpaceAlert - יש להפעיל אותה שוב אם שינינו את גודל הכונן (או לחלופין להוסיף התראה שקוראת לפרוצדורה בצורה אוטומטית כל שינוי בגודל הכונן) – בפתרון המצורף אני מקים Job שמגדיר את ההתראות כל בוקר מחדש.
ניתן להוריד את הקוד מכאן
חבילה 3 - ניטור פרואקטיבי של סף התפוסה של קובץ הלוג – מטרה לנטר אם לא בוצעו גיבויים בטווח מתאים.
* לחבילה נדרש שיהיה שרת מייל מוגדר לשרת הSQL.
החבילה מורכבת מ2 פרוצדורות:
Log. usp_Monitor_SetAlertLPercentLogUsed – מקבלת שם מסד נתונים, ויוצרת התראה ע"פ ההגדרה בטבלת Admin_].[General_Configuration]]
[Log].[usp_Monitor_GetLogPercentUsed] – פרוצדורה המופעלת ע"י Job(שנקרא ע"י התראה) ושולחת מייל בהתאם.
טבלאות-
[_Admin_].[General_Configuration] - טבלה לקביעת הסף בהתייחס למסד נתונים.
Job –
_Admin_:: MonitorLogPercentUsed - מופעל ע"י ההתראה
ניתן להוריד את הקוד מכאן
חבילה 4 - זיהוי טרנזקציות גדולות במסד הנתונים – פתרון לטווח הארוך
פרוצדורה
[Log].[usp_Monitor_FindLongRunningTransaction] – מקבלת 2 פרמטרים חשובים – דקות/שניות אשר נועדו לנטר טרנזקציות מעל הפרמטרים שהוזנו – כרגע 15 שניות.
Job
_Monitor_ :: FindLongRunningTransaction – מוגדר לעבוד כל 15 שניות ומפעיל את הפרוצדורה
התראה אופציונלית – עובדת רק כאשר מסד הנתונים מוגדר כrcsi - https://msdn.microsoft.com/en-us/library/ms173763.aspx
ניתן להוריד את הקוד מכאן
כל החבילות נראות פחות או יותר באותו מבנה כאשר יש להחליף את הפרמטרים הבאים לשימושך –
/******************************************* Read Here ********************************************/
/******************************************* Read Here *********************************************
replace :: <<YourTargetToMonitorDB>> with your Target Db For Log Monitor
replace :: <<YourMagDB>> with your managment Db on the server
replace :: YourMail@naya-tech.co.il with your eMail
replace :: <<YOUR OPERATOR>> @notify_email_operator_name NAME TO YOUR Operator
******************************************** Read Here ********************************************/
/******************************************* Read Here ********************************************/
לסיכום,
המאמר מכוסה פתרון חינמי לניטור ומציאת הסיבה לגדילת הלוג, ניתן להשתמש ע"מ למצוא את הסיבות והמקרים שנוכל לשפר בכדי למנוע גדילת לוג מסיבית. הטיפול צריך להיות מיידי כדי לא להחמיר עם הבעיה ולכן הוא עובד בשיטת Pro-Active.
אשמח לשמוע כיצד אתם מנטרים את גדילת הלוג אצלכם בארגון או אם הפתרון עזר למישהו נוסף.
קריאה בהרחבה:
8 Steps to better Transaction Log throughput By: Kimberly Tripp
