ปัญหา: งานตรวจสอบความสมบูรณ์ของฐานข้อมูล (Database integrity job) ล้มเหลวเนื่องจากปัญหา IOPS สำหรับฐานข้อมูล TestDB ซึ่งมีขนาดมากกว่า 2 TB เนื่องจากขนาดไฟล์ใหญ่ จึงทำให้การจัดการฐานข้อมูลเป็นเรื่องยาก
แนวทาง: เพื่อ troubleshoot ปัญหานี้ เราตัดสินใจแบ่งข้อมูลระหว่าง 2 data files ดังนั้นสถานะปัจจุบันของพื้นที่ไดรฟ์และ data file คือดังด้านล่าง:
Data file ของเราถูกโฮสต์อยู่ในไดรฟ์ N:\ และเราจะสร้างไฟล์อีกไฟล์หนึ่งในตำแหน่งเดียวกัน แนวทางของเราคือเริ่มการถ่ายโอนข้อมูลโดยใช้คำสั่ง emptyfile และหยุดคำสั่ง query ระหว่างทางด้วยตนเอง เพื่อหยุดการเคลื่อนย้ายข้อมูลอย่างบังคับ โปรดทราบว่าการหยุดคำสั่ง query ระหว่างทางด้วยตนเองจะไม่มีผลกระทบต่อฐานข้อมูล (ความสมบูรณ์/ความสอดคล้อง) จากนั้นเราจะ shrink ไฟล์ mdf เพื่อคืนพื้นที่ว่าง
วิธีแก้ไข: ปฏิบัติตามขั้นตอนด้านล่างเพื่อแบ่งข้อมูลระหว่างหลายไฟล์ข้อมูล SQL Server ขั้นแรก เราต้องเพิ่ม secondary data file ซึ่งเราจะนำข้อมูลของเราไปใส่ มันจะถูกเพิ่มเป็น ndf (next data file) รันสคริปต์ด้านล่างเพื่อเพิ่ม data file เพิ่มเติมบนฐานข้อมูล TestDB
USE [master] GO ALTER DATABASE [TestDB] ADD FILE ( NAME = N'TestDB_1', FILENAME = N'N:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB_1.ndf' , SIZE = 209715200KB , FILEGROWTH = 5242880KB ) TO FILEGROUP [PRIMARY]GO เมื่อคุณ execute สคริปต์นี้ มันจะเพิ่ม data file ใหม่ชื่อ TestDB_1 ในไดรฟ์ N:\ และขนาดจะเป็น 200 GB (เราเลือกขนาดนี้ตามบริบทของฐานข้อมูลของเรา) เราตั้งค่า file growth เป็น 5 GB และ data file จะถูกเพิ่มเข้าไปใน primary file group
ตอนนี้ หลังจากเพิ่ม data file เริ่มการดำเนินการ DBCC emptyfile บนฐานข้อมูล TestDB ไวยากรณ์จะเป็น:
use YOURDATABASE go dbcc shrinkfile(‘mdfFileName’,emptyfile)ดังนั้นในกรณีของเราจะเป็น:
USE [TestDB] go DBCC shrinkfile ('TestDB',emptyfile)ที่นี่ TestDB คือ logical name ของไฟล์ที่เราต้องการนำข้อมูลออก คือไฟล์ mdf ของเรา ตอนนี้เมื่อเราเริ่ม operation นี้ เราต้องติดตาม ว่าข้อมูลย้ายจาก mdf ไป ndf ไปเท่าไหร่ คุณสามารถใช้ query ด้านล่างเพื่อติดตามได้:
USE [TestDB] GO SELECT [TYPE] = A.TYPE_DESC ,[FILE_Name] = A.name ,[FILEGROUP_NAME] = fg.name ,[File_Location] = A.PHYSICAL_NAME ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) ,[USEDSPACE_%] = CAST((CAST(FILEPROPERTY(A.name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(A.size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100) ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id order by A.TYPE desc, A.NAME;สำหรับแนวทางของเรา เราต้องการให้ ndf อยู่ที่ประมาณ 500 GB ดังนั้นเมื่อ ndf ถึงขนาดนี้ เราสามารถหยุดการดำเนินการ emptyfile ได้ เมื่อหยุดการดำเนินการ emptyfile แล้ว เราต้องคืนพื้นที่ว่างใน mdf ด้วยตนเองโดยใช้ query ด้านล่าง:
DBCC Shrinkfile('TestDB', 1500000) -- เราต้องเปลี่ยนขนาดเป็นชิ้นเล็กๆ
ตอนนี้ mdf ของเราเป็น 2 TB เราย้าย 500 GB ไป ndf ดังนั้น 500 GB จึงสามารถคืนจาก mdf ได้ ซึ่งเราคืนพื้นที่ด้วย query ด้านบน
เราสามารถทำซ้ำขั้นตอนนี้หลายครั้ง เพื่อย้ายข้อมูลระหว่าง data files โดยหยุดการดำเนินการระหว่างทางตามพื้นที่จัดเก็บของเรา และคืนพื้นที่อีกครั้ง
สิ่งหนึ่งที่ต้องบันทึกไว้ขณะใช้ emptyfile บน mdf คือ คุณจะไม่สามารถว่างเปล่าเนื้อหาของ primary data file ที่มี file ID 1 ได้อย่างสมบูรณ์ เพื่อให้ได้หมายเลข file ID ให้รันสคริปต์นี้
select file_id, name,physical_name from sys.database_filesที่นี่ ในตัวอย่างนี้ ชื่อไฟล์คือ "mo" และ file_id คือ 1 เมื่อคุณพยายามว่างเปล่าไฟล์ mo ที่มี file_id 1 คุณจะพบข้อความข้อผิดพลาดนี้
นี่เพราะมีข้อมูลระบบภายในไฟล์ต้นฉบับ ซึ่งไม่สามารถว่างเปล่าได้ แต่ หากคุณลองคำสั่งเดียวกันบน data file อื่น "mo2data" คำสั่ง empty file จะสำเร็จ
สรุป (Conclusion)
เมื่อกิจกรรมการย้ายข้อมูลนี้เสร็จสมบูรณ์ โปรดรัน database maintenance jobs: – Index optimize job – Integrity check job – Full database backup job.
ใช้แท็บ Feedback เพื่อแสดงความคิดเห็นหรือถามคำถาม คุณยังสามารถเริ่มบทสนทนากับเราได้
No