אוק20
Written by:
ronen ariely
20/10/2013 06:40 
שרת ה SQL מנהל את קובץ הלוג הפיסי ביחידות וירטואליות הנקראות Virtual Log File או בקיצור VLF. לקבצים הווירטואליים אין גודל קבוע ואין כמות קבועה!
יחידות אלו הן מקשה אחת. מרגע שנוצרה יחידה וירטואלית כזו הרי שהיא לא יכולה להתפצל. הקובץ וירטואלי יחתך (truncated) רק אם כל העסקאות (transaction) הרשומות בו הסתיימו (ז"א עברו committed או roll back).
כאשר קובץ הלוג גדל, השרת מייצר יחידות וירטואליות נוספות. הגודל של היחידות החדשות יקבע לפי (1) הגודל שיש להוסיף לקובץ הפיסי ו(2) כמות הקבצים הווירטואליים שנוספים. כמות הקבצים שנוצרת נקבעת לפי הגודל שנוסף לקובץ הפיסי (נקבע במאפיין auto-growth בזמן יצירת הקובץ), וזאת לפי החוקיות הבאה:
* תוספת של עד 64MB תגרור הוספה של 4 קבצים.
*תוספת של עד 1GB תגרור הוספת 8 קבצים.
*תוספת של יותר מ 1GB תגרור הוספת 16 קבצים.
ניהול נכון של הקבצים הווירטואליים יכול להיות גורם מכריע בביצועים של מסד הנתונים. כמות קבצים גדולה מדי יכולה לגרור האטה בפעולות שונות (כמו גיבוי/שחזור) בשל הצורך לגשת למספר גדול, מצד שני גודל קבצים גדול מדי יגרום לכך ששחרור הקובץ ייקח זמן רב ויכול ליצור איטיות המערכת, מה גם שהדבר יכול לגרום לגדילה של הקובץ הפיסי ויצירת קובץ חדש במקום שימוש בקבצים לא פעילים שעדיין לא עברו truncated במלואם. במצב אידאלי גודל קובץ הלוג הפיזי נשמר קבוע, הקבצים הווירטואליים יהיו בגודל שווה (בקירוב), אחוז הקבצים הפעילים (status 2) לא יהיה גבוה מדי. כמות הקבצים לא תהיה גדולה מדי.
ניהול הקבצים הווירטואליים כולל שלוש שלבים: (1) בדיקת מספר הקבצים הווירטואליים שיש לנו בכל קובץ לוג, (2) קביעת המספר הרצוי של הקבצים הווירטואליים שלנו. (3) קביעת הערך של auto-growth, כיווץ הקובץ הפיסי, הגדלת הקובץ הפיסי על מנת לקבל את הגודל הרצוי.
זה זמן טוב לעבור ל SSMS
/****************************************************************************** */
/** */
/** Transaction Log File Structure */
/** */
/****************************************************************************** */
USE AdventureWorksDW2012
GO
------------------------------------------------ DBCC LOGINFO
DBCC LOGINFO('AdventureWorksDW2012')
GO
/*
חידה: למה מספר הקבצים לא תואם לכפולות של 4 כפי שהן נוצרים
-------------------------------------------------------------
*/
/* _______________________________________________ */
/* */
/* ====== לפירוט והסבר נעבור לאקסל ======== */
/* _______________________________________________ */
/* Q&A */
-- Q: DBCC LOGINFO returns many rows with status = 2
-- A: you have full recovery but haven't backed up the transaction log,
-- you have CDC enabled on the database,
-- or your are using replication / mirroring.
--
-- Check if CDC is Enable
select is_cdc_enabled
from sys.databases
where name = '_ArielyAccessoriesDB'
-- To check the reasone run:
select log_reuse_wait_desc,*
from sys.databases
where name = '_ArielyAccessoriesDB'
GO
-- * if the log_reuse_wait_desc = NOTHING then the number of rows with status = 2 should be one
DBCC LOGINFO(_ArielyAccessoriesDB)
GO
-- * If all you do is a FULL backup, the log is not cleared for reuse, only a LOG backup will free the VLF's for reuse under FULL recovery.
/* Helper Queries */
-- Get all log files size
DBCC SQLPERF(LOGSPACE);
GO
-- Get Open Transactions
DBCC OPENTRAN
GO
/*
Transaction information for database 'AdventureWorksDW2012'.
Oldest active transaction:
SPID (server process ID): 68
UID (user ID) : -1
Name : user_transaction
LSN : (1274:505:1)
Start time : Oct 18 2013 10:26:29:390PM
SID : 0x010500000000000515000000456f93ad8ebacc858991f193f1030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
.