Automatically Gathering Database Size

J Steven Jones
sjones@swynk.com

הקדמה

לאחרונה נשאלתי ע"י עמית שלי איך לאסוף מידע מסוים אודות שרת ה-SQL על בסיס תקופתי. אותו עמית נזקק לדיווח על נתונים ואחסונם כדי לבצע אנליזה לאורך זמן. מאמר זה עונה לאחד מנושאי הנתונים - מציאת גודלו של כל מסד נתונים בשרת באופן דינמי.

שיטת העבודה

על מנת לקבל את הגודל של כל מסד נתונים באופן דינמי, עליך לשלוף את המידע הפנימי של שרת ה-SQL המאוחסן במסד הנתונים הראשי (master database). בעבר, מפתחי SQL רבים (כולל אותי) שלפו את הנתונים מטבלאות המערכת בצורה ישירה. אולם, ישנה בעיה אחת עם השיטה הזאת. כאשר גרסא 7.0 של SQL Server יצאה לשוק, חלק מההגדרות של טבלאות המערכת שונו מגרסא 6.5 וכתוצאה מכך שאילתות רבות שכתבתי הפסיקו לעבוד. האשמה במקרה זה מוטלת עלי כיוון שבכל הדוקומנטציה של SQL Server מזהירים מפני שימוש בטבלאות המערכת בכתיבת הקוד ועל מנת להתאים את הקוד שלי נאלצתי להתאים מספר פרוצדורות שמורות לעבוד עם הגדרות הטבלאות החדשות .

אם אתה לא אמור לשלוף את המידע ישירות מטבלאות מערכת, מה עליך לעשות ?

החל מגרסא 7.0 מיקרוסופט החלה לספק מראות תכנית מידע (Information Schema views) של מידע במערכת שביכולתך להשתמש בהם בפיתוח מבלי לחשוש מהתמדה בגרסאות עתידיות. מראות אלו נקראות מראות תכנית מידע מכיוון שה"בעלים" של מראות אלו היא תכנית המידע - "Information_Schema" . דוגמא אחת למראות אלו היא מראת ה-"Tables" המספקת רשימה של טבלאות ומראות בכל מסד נתונים. אם תריץ את השאילתא הבאה :

Select * from information_schema.tables      

תקבל רשימה של טבלאות ומספר פיסות נוספות של מידע. דבר זה שימושי אם אתה צריך לעבוד עם רשימה של טבלאות בקוד. כך שאנחנו רק מריצים את השאילתא הבאה על מנת לקבל את הגדלים של מסדי הנתונים שלנו :

Select * from master..information_schema.databases      

ובכן, נסה לבצע זאת ואם תקבל חזרה את הגדלים, שלך לי דואר אלקטרוני ואני אדאג לשכתב את המאמר. כאשר אני ניסיתי מצאתי, להפתעתי הרבה, שלא היו שום מראות "databases".ובכן, האם אנו בכל זאת חייבים לשלוף את המידע ישירות מטבלאות מערכת ? אני זוכר שציינתי בפניך שזה אינו רעיון כל כך טוב ואינו מומלץ ע"י מיקרוסופט.

אל פחד, קיימת שיטה נוספת. לאחר "חפירה" קטנה, ייצרתי את הקוד הבא :

Create table #mydbs (dbname char( 50), size char( 20), dbowner char( 50), dbid int, crdate datetime, status char( 100))   Insert #mydbs  Exec sp_helpdb   Select * from #mydbs /* */ Drop table #mydbs

מדוע ייצרתי טבלה? ואז מדוע השמטתי אותה? ולמה ישנה הערה ריקה? אני צריך את המידע הזה, נכון?

למעשה זוהי טכניקה נפלאה להתמודדות עם קבוצות תוצאות ב SQL Server גרסאות 7.0 ומטה. ב SQL Server גרסא 2000 ביכולתנו לקבל טיפוס נתונים טבלאי ולמעשה להעביר קבוצות תוצאות לפרוצדורות שמורות ומהן, אבל נכון לרגע זה, זוהי השיטה הטובה ביותר שמצאתי להתמודדות עם קבוצת תוצאה בתוך פרוצדורה שמורה. היות שאין ביכולתי להתמודד עם יותר מערך אחד ב- T-SQL  (גרסאות 7.0 ומטה), אני יוצר טבלה זמנית שתכיל את תוצאות הפרוצדורה השמורה הנקראת על ידי. שיטה זו אינה נפוצה כל כך בקוד שראיתי, אבל היא נכונה לחלוטין.

כאשר התחלתי בכתיבת מאמר זה, ראשית הרצתי את הפרוצדורה השמורה ולאחר מכן יצרתי את הגדרת הטבלה באמצעות התוצאות. אם תריץ אצווה (batch) זו על השרת שלך, אתה אמור לראות את אותן תוצאות שהיית מקבל ע"י הרצת sp_helpdb בצורה ישירה.

אז מה עושים עם תוצאת אלו ?

כל דבר העולה בראשך! הקטע של האצווה שלי המכיל את סימוני ההערה יציין את המקום אליו עיבוד התוצאות יכוון.  ביכולתך לכתוב שאילתא המחזירה דו"ח על גדלי מסד הנתונים. או שביכולתך להכניס את הנתונים האלה לתוך טבלה קבועה ולעקוב אחר גידולו של  מסד הנתונים לאורך זמן.