[IT168 Server Academy] บันทึกธุรกรรมเป็นส่วนที่สำคัญมาก แต่มักถูกละเลยในโครงสร้างฐานข้อมูล เนื่องจากไม่ได้ใช้งานเหมือนกับสคีมาในฐานข้อมูล จึงมีเพียงไม่กี่คนที่ให้ความสนใจกับบันทึกธุรกรรม
บันทึกธุรกรรมคือบันทึกการเปลี่ยนแปลงฐานข้อมูล สามารถบันทึกการดำเนินการใด ๆ ในฐานข้อมูลและบันทึกผลการบันทึกเป็นไฟล์แยกต่างหาก สำหรับทุกขั้นตอนของธุรกรรม บันทึกธุรกรรมจะมีบันทึกที่ครอบคลุมมาก และไฟล์ข้อมูลสามารถกู้คืนเป็นสถานะก่อนการทำธุรกรรมได้โดยอิงจากบันทึกเหล่านี้ จากจุดเริ่มต้นของการทำธุรกรรม บันทึกธุรกรรมจะอยู่ในสถานะการบันทึก การดำเนินการใดๆ บนฐานข้อมูลระหว่างการทำธุรกรรมจะอยู่ภายในขอบเขตการบันทึก การบันทึกจะไม่สมบูรณ์จนกว่าผู้ใช้จะคลิกส่งหรือย้อนกลับ แต่ละฐานข้อมูลมีบันทึกธุรกรรมอย่างน้อยหนึ่งรายการและไฟล์ข้อมูลหนึ่งไฟล์
เพื่อเหตุผลด้านประสิทธิภาพ SQL Server จะจัดเก็บการเปลี่ยนแปลงของผู้ใช้ไว้ในแคช การเปลี่ยนแปลงเหล่านี้จะถูกเขียนลงในบันทึกธุรกรรมทันที แต่ไม่ใช่ในไฟล์ข้อมูล บันทึกธุรกรรมใช้จุดทำเครื่องหมายเพื่อตรวจสอบว่าธุรกรรมได้เขียนข้อมูลจากแคชไปยังไฟล์ข้อมูลหรือไม่ เมื่อ SQL Server เริ่มระบบใหม่ ระบบจะตรวจสอบจุดทำเครื่องหมายล่าสุดในบันทึกและลบบันทึกธุรกรรมหลังจากจุดทำเครื่องหมายนี้ เนื่องจากบันทึกธุรกรรมเหล่านี้ไม่ได้เขียนข้อมูลในแคชลงในไฟล์ข้อมูลจริงๆ สิ่งนี้จะป้องกันไม่ให้ธุรกรรมที่ถูกขัดจังหวะเหล่านั้นแก้ไขไฟล์ข้อมูล
รักษาบันทึกธุรกรรม
เนื่องจากหลายๆ คนมักลืมบันทึกธุรกรรม จึงอาจนำปัญหามาสู่ระบบได้เช่นกัน ในขณะที่ระบบยังคงทำงานต่อไป บันทึกบันทึกจะถูกบันทึกมากขึ้นเรื่อยๆ และขนาดของไฟล์บันทึกก็จะใหญ่ขึ้นเรื่อยๆ ส่งผลให้พื้นที่ดิสก์ไม่เพียงพอในที่สุด เว้นแต่ว่าจะมีการล้างบันทึกบ่อยครั้งในการทำงานประจำวัน ในที่สุดไฟล์บันทึกก็จะครอบครองพื้นที่ว่างทั้งหมดในพาร์ติชัน การกำหนดค่าเริ่มต้นของบันทึกคือความจุไม่จำกัด หากคุณทำงานในการกำหนดค่านี้ ระบบจะขยายต่อไปและจะใช้พื้นที่ว่างทั้งหมดในที่สุด ทั้งสองสถานการณ์อาจทำให้ฐานข้อมูลหยุดทำงาน
การสำรองข้อมูลบันทึกธุรกรรมเป็นประจำสามารถป้องกันไฟล์บันทึกไม่ให้ใช้พื้นที่ดิสก์มากเกินไปได้อย่างมีประสิทธิภาพ กระบวนการสำรองข้อมูลจะตัดทอนส่วนของบันทึกที่ไม่จำเป็นอีกต่อไป วิธีการตัดทอนคือการทำเครื่องหมายบันทึกเก่าว่าไม่ได้ใช้งาน จากนั้นจึงเขียนทับบันทึกใหม่ที่ตำแหน่งของบันทึกเก่า เพื่อป้องกันไม่ให้บันทึกธุรกรรมมีขนาดเพิ่มขึ้น หากไม่สามารถสำรองข้อมูลบันทึกเป็นประจำได้ วิธีที่ดีที่สุดคือตั้งค่าฐานข้อมูลเป็น "รูปแบบการกู้คืนแบบง่าย" ในโหมดนี้ ระบบจะบังคับให้บันทึกธุรกรรมตัดทอนโดยอัตโนมัติทุกครั้งที่มีการบันทึกจุดทำเครื่องหมาย โดยเขียนทับบันทึกเก่าด้วยบันทึกใหม่
กระบวนการตัดทอนเกิดขึ้นเมื่อสำรองข้อมูลหรือทำเครื่องหมายจุดเก่าว่าไม่ได้ใช้งาน ซึ่งช่วยให้สามารถเขียนทับบันทึกธุรกรรมเก่าได้ แต่ไม่ได้ลดพื้นที่ดิสก์จริงที่ครอบครองโดยบันทึกธุรกรรม แม้ว่าจะไม่ได้ใช้บันทึกอีกต่อไป แต่ก็ยังคงใช้พื้นที่จำนวนหนึ่ง ดังนั้นในระหว่างการบำรุงรักษา บันทึกธุรกรรมจึงต้องถูกบีบอัดด้วย บันทึกธุรกรรมจะถูกบีบอัดโดยการลบบันทึกที่ไม่ได้ใช้งาน ซึ่งจะช่วยลดพื้นที่ฮาร์ดไดรฟ์ทางกายภาพของไฟล์บันทึก
แฟ้มบันทึกธุรกรรมของฐานข้อมูลปัจจุบันสามารถบีบอัดได้โดยใช้คำสั่ง DBCC SHRINKDATABASE คำสั่ง DBCC SHRINKFILE ใช้เพื่อบีบอัดแฟ้มบันทึกธุรกรรมที่ระบุ นอกจากนี้ การดำเนินการบีบอัดอัตโนมัติยังสามารถเปิดใช้งานในฐานข้อมูลได้อีกด้วย เมื่อบันทึกถูกบีบอัด บันทึกเก่าจะถูกทำเครื่องหมายว่าไม่ได้ใช้งานก่อน จากนั้นบันทึกที่ทำเครื่องหมายว่าไม่ได้ใช้งานจะถูกลบออกทั้งหมด ขึ้นอยู่กับวิธีการบีบอัดที่ใช้ คุณอาจไม่เห็นผลทันที ตามหลักการแล้ว งานบีบอัดควรดำเนินการในช่วงเวลาที่ระบบไม่ยุ่งมาก มิฉะนั้นอาจส่งผลต่อประสิทธิภาพของฐานข้อมูล
การคืนค่าการสำรองข้อมูลบันทึกธุรกรรมของฐานข้อมูล
สามารถใช้เพื่อคืนค่าฐานข้อมูลไปยังสถานะที่ระบุ แต่การสำรองข้อมูลบันทึกธุรกรรมนั้นไม่เพียงพอที่จะดำเนินการกู้คืนฐานข้อมูลให้เสร็จสมบูรณ์ และไฟล์ข้อมูลที่สำรองไว้ก็ต้องมีส่วนร่วมในการกู้คืนด้วย เมื่อกู้คืนฐานข้อมูล ขั้นตอนแรกคือการคืนค่าไฟล์ข้อมูล อย่าตั้งค่าไฟล์ข้อมูลทั้งหมดเป็นสถานะเสร็จสมบูรณ์จนกว่าไฟล์ข้อมูลทั้งหมดจะได้รับการกู้คืน ไม่เช่นนั้นบันทึกธุรกรรมจะไม่ได้รับการกู้คืน เมื่อการกู้คืนไฟล์ข้อมูลเสร็จสิ้น ระบบจะกู้คืนฐานข้อมูลให้อยู่ในสถานะที่ผู้ใช้ต้องการ ผ่านการสำรองบันทึกธุรกรรม หากมีการสำรองข้อมูลไฟล์บันทึกหลายไฟล์หลังจากการสำรองฐานข้อมูลครั้งล่าสุด โปรแกรมสำรองข้อมูลจะกู้คืนตามลำดับตามเวลาที่สร้างขึ้น
กระบวนการอื่นที่เรียกว่าการขนส่งบันทึกสามารถให้ความสามารถในการสำรองฐานข้อมูลที่แข็งแกร่งยิ่งขึ้น เมื่อมีการกำหนดค่าการจัดส่งบันทึก จะสามารถคัดลอกฐานข้อมูลทั้งหมดไปยังเซิร์ฟเวอร์อื่นได้ ในกรณีนี้ บันทึกธุรกรรมจะถูกส่งไปยังเซิร์ฟเวอร์สำรองเป็นระยะๆ เพื่อการกู้คืนข้อมูล ซึ่งจะทำให้เซิร์ฟเวอร์อยู่ในสถานะการสำรองข้อมูลแบบ hot โดยอัปเดตเมื่อข้อมูลมีการเปลี่ยนแปลง เซิร์ฟเวอร์อื่นเรียกว่าเซิร์ฟเวอร์มอนิเตอร์ และสามารถใช้เพื่อติดตามสัญญาณการจัดส่งที่ส่งตามช่วงเวลาที่กำหนด หากไม่ได้รับสัญญาณภายในเวลาที่กำหนด เซิร์ฟเวอร์การมอนิเตอร์จะบันทึกเหตุการณ์นี้ลงในบันทึกเหตุการณ์ กลไกนี้ทำให้การขนส่งบันทึกมักใช้ในแผนการกู้คืนความเสียหาย
บันทึกธุรกรรมการเพิ่มประสิทธิภาพ
มีบทบาทสำคัญในฐานข้อมูล และยังมีผลกระทบบางอย่างต่อประสิทธิภาพโดยรวมของระบบด้วย ด้วยตัวเลือกต่างๆ มากมาย เราจึงสามารถเพิ่มประสิทธิภาพการทำงานของบันทึกธุรกรรมได้ เนื่องจากบันทึกธุรกรรมเป็นกระบวนการเขียนดิสก์อย่างต่อเนื่อง จึงไม่มีการอ่านเกิดขึ้นในระหว่างกระบวนการนี้ ดังนั้นการวางไฟล์บันทึกบนดิสก์อิสระจะมีบทบาทบางอย่างในการเพิ่มประสิทธิภาพ
มาตรการปรับให้เหมาะสมอีกประการหนึ่งเกี่ยวข้องกับขนาดไฟล์บันทึก เราสามารถกำหนดขนาดของไฟล์บันทึกให้ไม่เกินสองสามเปอร์เซ็นต์ของพื้นที่ฮาร์ดดิสก์หรือกำหนดขนาดของไฟล์ได้ หากตั้งค่าไว้สูงเกินไป จะเปลืองเนื้อที่ดิสก์ และหากตั้งค่าน้อยเกินไป จะบังคับให้ไฟล์บันทึกพยายามขยายอย่างต่อเนื่อง ทำให้ประสิทธิภาพของฐานข้อมูลลดลง
ไฟล์บันทึกธุรกรรม ไฟล์บันทึกธุรกรรมเป็นไฟล์ที่ใช้ในการบันทึกการอัพเดตฐานข้อมูล โดยมีนามสกุลเป็น ldf
ใน SQL Server 7.0 และ SQL Server 2000 ถ้ามีการตั้งค่าคุณลักษณะการเติบโตอัตโนมัติ แฟ้มบันทึกธุรกรรมจะขยายโดยอัตโนมัติ
โดยทั่วไป ขนาดของบันทึกธุรกรรมจะมีเสถียรภาพเมื่อสามารถรองรับจำนวนธุรกรรมสูงสุดที่เกิดขึ้นระหว่างการตัดทอนบันทึกธุรกรรม ซึ่งถูกทริกเกอร์โดยจุดตรวจสอบหรือการสำรองข้อมูลบันทึกธุรกรรม
อย่างไรก็ตาม ในบางกรณี บันทึกธุรกรรมอาจมีขนาดใหญ่มากจนไม่มีเนื้อที่หรือเต็ม โดยทั่วไป เมื่อแฟ้มบันทึกธุรกรรมเต็มพื้นที่ดิสก์ที่มีอยู่ และไม่สามารถขยายได้อีกต่อไป คุณจะได้รับข้อความแสดงข้อผิดพลาดดังต่อไปนี้:
ข้อผิดพลาด:9002 ความรุนแรง:17 สถานะ:2
ไฟล์บันทึกสำหรับฐานข้อมูล '%.*ls' เต็มแล้ว
นอกเหนือจากข้อความแสดงข้อผิดพลาดนี้ SQL Server อาจทำเครื่องหมายฐานข้อมูลว่า SUSPECT เนื่องจากไม่มีพื้นที่ส่วนขยายบันทึกธุรกรรม สำหรับข้อมูลเพิ่มเติมเกี่ยวกับวิธีการกู้คืนจากสถานการณ์นี้ ดูหัวข้อ "เนื้อที่ดิสก์เหลือน้อย" ในวิธีใช้แบบออนไลน์ของเซิร์ฟเวอร์ SQL
นอกจากนี้ การขยายบันทึกธุรกรรมอาจทำให้เกิดสถานการณ์ต่อไปนี้:
· ไฟล์บันทึกธุรกรรมที่มีขนาดใหญ่มาก
· ธุรกรรมอาจล้มเหลวและอาจเริ่มย้อนกลับ
· ธุรกรรมอาจใช้เวลานานจึงจะเสร็จสมบูรณ์
· ปัญหาด้านประสิทธิภาพอาจเกิดขึ้น
· อาจเกิดการอุดตันได้
สาเหตุการขยายบันทึกธุรกรรมสามารถเกิดขึ้นได้เนื่องจากเหตุผลหรือสถานการณ์ต่อไปนี้:
· ธุรกรรมที่ไม่มีข้อผูกมัด · ธุรกรรมขนาดใหญ่มาก · การดำเนินการ: DBCC DBREINDEX และ CREATE INDEX
· เมื่อกู้คืนจากการสำรองข้อมูลบันทึกธุรกรรม · แอปพลิเคชันไคลเอนต์ไม่ประมวลผลผลลัพธ์ทั้งหมด · แบบสอบถามหมดเวลาก่อนที่บันทึกธุรกรรมจะขยายเสร็จสมบูรณ์และคุณได้รับข้อความแสดงข้อผิดพลาด "บันทึกเต็ม" ที่เป็นเท็จ · การแก้ปัญหาธุรกรรมที่ไม่จำลองแบบที่เกิดจากไฟล์บันทึกถูก full เมื่อฐานข้อมูล SQL ไม่สามารถเขียนลงไฟล์ได้ มีสองวิธีที่สามารถใช้ได้:
วิธีหนึ่ง: ล้างบันทึก
1. เปิดตัววิเคราะห์แบบสอบถามและป้อนคำสั่ง DUMP TRANSACTION ชื่อฐานข้อมูลที่มี NO_LOG
2. เปิด Enterprise Manager อีกครั้ง -- คลิกขวาที่ฐานข้อมูลที่คุณต้องการบีบอัด -- งานทั้งหมด -- ย่อขนาดฐานข้อมูล -- ย่อขนาดไฟล์ -- เลือกไฟล์บันทึก -- เลือกย่อขนาดเป็น XXM ในโหมดย่อขนาด และ จะมีการอนุญาตให้ย่อขนาดที่นี่ หากต้องการเข้าถึงจำนวน M ขั้นต่ำ ให้ป้อนหมายเลขนี้โดยตรงและยืนยัน
อีกวิธีหนึ่งมีความเสี่ยง เนื่องจากไฟล์บันทึกของ SQL SERVER ไม่ได้ถูกเขียนลงในไฟล์ฐานข้อมูลหลักทันที หากไม่ได้รับการจัดการอย่างเหมาะสม จะทำให้ข้อมูลสูญหาย
1: ลบบันทึก
แยกฐานข้อมูล ตัวจัดการองค์กร -> เซิร์ฟเวอร์ -> ฐานข้อมูล -> คลิกขวา -> แยกฐานข้อมูล 2: ลบไฟล์ LOG แนบฐานข้อมูล ตัวจัดการองค์กร -> เซิร์ฟเวอร์ -> ฐานข้อมูล -> คลิกขวา -> แนบฐานข้อมูล วิธีการนี้สร้างบันทึกใหม่ขนาด เป็นเพียงมากกว่า 500K
หมายเหตุ: ขอแนะนำให้ใช้วิธีแรก
หากในอนาคตไม่อยากให้มันใหญ่ขึ้น
ใช้ภายใต้ SQL2000:
คลิกขวาที่ฐานข้อมูล -> คุณสมบัติ -> ตัวเลือก -> การกู้คืนความล้มเหลว- รุ่น- เลือก- โมเดลแบบง่าย
หรือใช้คำสั่ง SQL:
แก้ไขฐานข้อมูลชุดชื่อฐานข้อมูลการกู้คืนง่าย
นอกจากนี้ ดังแสดงในรูปด้านบน คุณสมบัติฐานข้อมูลมีสองตัวเลือก ที่เกี่ยวข้องกับการเติบโตของบันทึกธุรกรรม:
ตัดทอนการเข้าสู่ระบบจุดตรวจ
(ตัวเลือกนี้ใช้ใน SQL7.0 ใน SQL 2000 โมเดลการกู้คืนข้อผิดพลาดจะถูกเลือกเป็นโมเดลอย่างง่าย)
เมื่อดำเนินการคำสั่ง CHECKPOINT เนื้อหาของไฟล์บันทึกธุรกรรมจะถูกล้างหากเกินขนาด 70% ตั้งค่าตัวเลือกนี้เป็น True เสมอเมื่อพัฒนาฐานข้อมูล
หดอัตโนมัติ
ตรวจสอบฐานข้อมูลเป็นประจำ เมื่อพื้นที่ที่ไม่ได้ใช้ของไฟล์ฐานข้อมูลหรือไฟล์บันทึกเกินขนาด 25% ระบบจะลดขนาดไฟล์โดยอัตโนมัติเพื่อให้พื้นที่ที่ไม่ได้ใช้เท่ากับ 25% เมื่อขนาดไฟล์ไม่เกินขนาดเริ่มต้น เมื่อสร้างขึ้นจะไม่ ไฟล์ที่ลดลงจะต้องมีขนาดใหญ่กว่าหรือเท่ากับขนาดดั้งเดิม การลดขนาดไฟล์บันทึกธุรกรรมสามารถทำได้เฉพาะเมื่อสำรองข้อมูลหรือเมื่อตั้งค่าตัวเลือก Truncate log on checkpoint เป็น True
หมายเหตุ: โดยทั่วไป คุณสมบัติเริ่มต้นของฐานข้อมูลที่สร้างโดย Li Cheng ได้ถูกตั้งค่าไว้แล้ว แต่ในกรณีที่เกิดเหตุการณ์ที่ไม่คาดคิด คุณสมบัติของฐานข้อมูลจะมีการเปลี่ยนแปลง โปรดล้างบันทึก จากนั้นตรวจสอบคุณสมบัติด้านบนของฐานข้อมูลเพื่อป้องกันบันทึกธุรกรรม เติมเต็มอีกครั้ง