Di chuyển dữ liệu từ tệp mdf sang tệp ndf trong cùng một nhóm tệp

Vấn đề: Công việc toàn vẹn cơ sở dữ liệu không thành công do sự cố IOPS đối với cơ sở dữ liệu TestDB có kích thước lớn hơn 2 TB. Do kích thước tệp lớn, việc quản lý cơ sở dữ liệu trở nên khó khăn.

Phương pháp tiếp cận: Để khắc phục sự cố này, chúng tôi quyết định tách dữ liệu giữa 2 tệp dữ liệu. Vì vậy, trạng thái hiện tại của không gian ổ đĩa và tệp dữ liệu như sau:

Giải pháp :Thực hiện theo các bước dưới đây để tách dữ liệu giữa nhiều tệp dữ liệu SQL Server. Đầu tiên, chúng ta cần thêm một tệp dữ liệu thứ cấp mà chúng ta sẽ chèn dữ liệu vào. Nó sẽ được thêm vào dưới dạng ndf (tệp dữ liệu tiếp theo). Chạy tập lệnh bên dưới để thêm tệp dữ liệu bổ sung trên cơ sở dữ liệu 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 

Sau khi bạn thực thi tập lệnh này, nó sẽ thêm một tệp dữ liệu mới có tên TestDB_1 trong ổ N:\ và kích thước sẽ là 200 GB (Chúng tôi đã lấy điều này cùng với ngữ cảnh vào cơ sở dữ liệu của mình). Chúng tôi đã đặt tốc độ tăng tệp là 5 GB và tệp dữ liệu sẽ được thêm vào nhóm tệp chính.

Bây giờ, sau khi thêm tệp dữ liệu, hãy bắt đầu thao tác với tệp trống DBCC trên cơ sở dữ liệu TestDB. Cú pháp sẽ là:

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

Vì vậy, trong trường hợp của chúng tôi, nó sẽ là:

USE [TestDB]

go

DBCC shrinkfile ('TestDB’,emptyfile)

Ở đây TestDB là tên logic của tệp mà chúng ta muốn xóa dữ liệu, tức là tệp mdf của chúng ta. Bây giờ khi chúng ta bắt đầu thao tác này, chúng ta cần theo dõi lượng dữ liệu được chuyển từ mdf sang ndf. Bạn có thể sử dụng truy vấn dưới đây để giữ nguyên bản theo dõi:

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;

Đối với cách tiếp cận của chúng tôi, chúng tôi muốn ndf ở khoảng 500 GB, vì vậy khi ndf đạt đến kích thước này, chúng tôi có thể dừng hoạt động của tệp trống. :

DBCC Shrinkfile('TestDB', 1500000) --  

Chúng tôi cần thay đổi kích thước thành nhiều phần nhỏ hơn

Bây giờ mdf của chúng tôi là 2 TB, chúng tôi đã chuyển 500 GB sang ndf, do đó 500 GB có thể lấy lại được từ mdf, mà chúng tôi vừa lấy lại bằng cách sử dụng truy vấn trên.

Chúng tôi có thể lặp lại bước này nhiều lần để di chuyển dữ liệu giữa các tệp dữ liệu, dừng hoạt động giữa các tệp theo cách thủ công tùy theo bộ nhớ của chúng tôi và sau đó lấy lại dung lượng.

Một điều cần lưu ý khi sử dụng tệp trống trên mdf là bạn sẽ không thể làm trống hoàn toàn nội dung của tệp dữ liệu chính có ID tệp 1. Để nhận số ID tệp, hãy chạy tập lệnh này.

select file_id, name,physical_name from sys.database_files

Ở đây, trong ví dụ này, tên tệp là “mo” và tệp_id là 1. Khi bạn thử làm trống tệp mo có tệp_id 1, bạn sẽ gặp phải thông báo lỗi này.

Điều này là do có thông tin hệ thống trong tệp gốc, không thể được làm trống. Tuy nhiên, nếu bạn thử lệnh tương tự trên tệp dữ liệu khác “mo2data”, lệnh tệp trống sẽ thành công.

Kết luận

Khi hoạt động di chuyển dữ liệu này hoàn tất, hãy chạy các công việc bảo trì cơ sở dữ liệu:- Công việc tối ưu hóa chỉ mục– Công việc kiểm tra tính toàn vẹn– Công việc sao lưu toàn bộ cơ sở dữ liệu.

Sử dụng tab Phản hồi để đưa ra bất kỳ nhận xét hoặc đặt câu hỏi nào. Bạn cũng có thể bắt đầu cuộc trò chuyện với chúng tôi.