ย้ายข้อมูลจากไฟล์ mdf ไปยังไฟล์ ndf ในกลุ่มไฟล์เดียวกัน

ปัญหา: งานความสมบูรณ์ของฐานข้อมูลล้มเหลวเนื่องจากปัญหา IOPS สำหรับฐานข้อมูล TestDB ซึ่งมีขนาดมากกว่า 2 TB เนื่องจากไฟล์มีขนาดใหญ่ ทำให้การจัดการฐานข้อมูลทำได้ยาก

แนวทาง: เพื่อแก้ปัญหานี้ เราตัดสินใจแยกข้อมูลระหว่างไฟล์ข้อมูล 2 ไฟล์ สถานะปัจจุบันของพื้นที่ว่างในไดรฟ์และไฟล์ข้อมูลมีดังนี้:

วิธีแก้ปัญหา :ทำตามขั้นตอนด้านล่างเพื่อแยกข้อมูลระหว่างไฟล์ข้อมูล SQL Server หลายไฟล์ อันดับแรก เราต้องเพิ่มไฟล์ข้อมูลรองที่เราจะแทรกข้อมูล มันจะถูกเพิ่มเป็น ndf (ไฟล์ข้อมูลถัดไป) เรียกใช้สคริปต์ด้านล่างเพื่อเพิ่มไฟล์ข้อมูลเพิ่มเติมในฐานข้อมูล 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 

เมื่อคุณรันสคริปต์นี้ มันจะเพิ่มไฟล์ข้อมูลใหม่ที่ชื่อ TestDB_1 ในไดรฟ์ N:\ และขนาดจะเป็น 200 GB (เราใช้สิ่งนี้พร้อมกับบริบทในฐานข้อมูลของเรา) เราได้ตั้งค่าการเติบโตของไฟล์ไว้ที่ 5 GB และไฟล์ข้อมูลจะถูกเพิ่มในกลุ่มไฟล์หลัก

ตอนนี้หลังจากเพิ่มไฟล์ข้อมูลแล้วให้เริ่มดำเนินการ DBCC emptyfile บนฐานข้อมูล TestDB ไวยากรณ์จะเป็น:

use YOURDATABASE
go
dbcc shrinkfile(‘mdfFileName’,emptyfile)

ดังนั้นในกรณีของเราจะเป็น:

USE [TestDB]

go

DBCC shrinkfile ('TestDB’,emptyfile)

ที่นี่ TestDB เป็นชื่อตรรกะของไฟล์ที่เราต้องการลบข้อมูล เช่น ไฟล์ mdf ของเรา ตอนนี้เมื่อเราเริ่มดำเนินการนี้แล้ว เราจำเป็นต้องติดตามว่าข้อมูลถูกย้ายจาก mdf ไปยัง ndf มากน้อยเพียงใด คุณสามารถใช้แบบสอบถามด้านล่างเพื่อติดตามสิ่งเดียวกัน:

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 หยุดลง เราจำเป็นต้องเรียกคืนพื้นที่ว่างใน mdf ด้วยตนเองโดยใช้ข้อความค้นหาด้านล่าง :

DBCC Shrinkfile('TestDB', 1500000) --  

เราจำเป็นต้องเปลี่ยนขนาดเป็นชิ้นเล็ก ๆ

ตอนนี้ mdf ของเราคือ 2 TB เราย้าย 500 GB เป็น ndf ดังนั้น 500 GB สามารถเรียกคืนได้จาก mdf ซึ่งเราเพิ่งเรียกคืนโดยใช้ข้อความค้นหาด้านบน

เราสามารถทำซ้ำขั้นตอนนี้ได้หลายครั้งเพื่อย้ายข้อมูลระหว่างไฟล์ข้อมูล หยุดการดำเนินการระหว่างนั้นด้วยตนเองตามพื้นที่เก็บข้อมูลของเรา แล้วเรียกคืนพื้นที่อีกครั้ง

สิ่งหนึ่งที่ควรทราบในขณะที่ใช้ไฟล์ emptyfile บน mdf นั้น คุณจะไม่สามารถล้างเนื้อหาของไฟล์ข้อมูลหลักด้วย ID ไฟล์ 1 ได้อย่างสมบูรณ์ เรียกใช้สคริปต์นี้เพื่อรับหมายเลข ID ของไฟล์

select file_id, name,physical_name from sys.database_files

ในตัวอย่างนี้ ชื่อไฟล์คือ “mo” และ file_id คือ 1 เมื่อคุณพยายามล้างไฟล์ mo ที่มี file_id 1 คุณจะพบข้อความแสดงข้อผิดพลาดนี้

เนื่องจากมีข้อมูลระบบอยู่ในไฟล์ต้นฉบับซึ่งไม่สามารถล้างข้อมูลได้ แต่ถ้าคุณลองใช้คำสั่งเดียวกันกับไฟล์ข้อมูลอื่น “mo2data” คำสั่งไฟล์ว่างจะสำเร็จ

บทสรุป

เมื่อกิจกรรมการย้ายข้อมูลเสร็จสิ้น โปรดเรียกใช้งานบำรุงรักษาฐานข้อมูล:– งานเพิ่มประสิทธิภาพดัชนี– งานตรวจสอบความสมบูรณ์– งานสำรองฐานข้อมูลแบบเต็ม

ใช้แท็บคำติชมเพื่อแสดงความคิดเห็นหรือถามคำถาม คุณสามารถเริ่มการสนทนากับเราได้เช่นกัน