Những hư hỏng trong Database thường rất it xảy ra, nhưng việc phát hiện và khắc phục ngay các hư hỏng này là việc làm rất quan trọng của DBA. SQL server có thể làm việc trong thời gian dài mà không cần bảo dưỡng, tuy nhiên để hệ thông làm việc an toàn và ổn định cần có lịch bảo dưỡng định kỳ
SQL server Index cũng có thể làm việc liên tục mà không cần bảo dưỡng, nhưng hệ thống sẽ làm việc tốt hơn nếu định kỳ loại bỏ sự phân mảnh trong database.
SQL server sử dụng Maintenance Plan Wizard tạo ra SQL Server Agent jobs để thực hiện các tác vụ bảo trì database theo định kỳ.
Thực hiện xong bài lab này, bạn có thể biết cách sử dụng lệnh DBCC CHECKDB để kiểm tra lỗi database, loại bỏ phân mảnh trong database (Maintaining Indexes), và lập lịch bảo trì database định kỳ.
Thực Hiện Bảo Trì Database
A. Sử dụng lệnh DBCC CHECKDB:
Tại SSMS, mở file DBCCCheckDB.sql trong folder module 11, chọn các lệnh trong phần Run DBCC CHECKDB with default options, ấn F5 để kiểm tra tính toàn vẹn của AdventureWorks database và hiển thị thông tin chi tiết,
— Run DBCC CHECKDB with default options
DBCC CHECKDB(‘AdventureWorks’);
GO
Chọn các lệnh trong phần Run DBCC CHECKDB without informational messages, ấn F5 để kiểm tra tính toàn vẹn của AdventureWorks database và chỉ hiện thị thông tin khi có lỗi
— Run DBCC CHECKDB without informational messages
DBCC CHECKDB(‘AdventureWorks’) WITH NO_INFOMSGS;
GO
Chọn các lệnh trong phần Run DBCC CHECKDB against CorruptDB, ấn F5 để kiểm tra tính toàn vẹn của CorruptDB database và thấy có một số lỗi trong dbo.Orders table.
— Run DBCC CHECKDB against CorruptDB
DBCC CHECKDB(‘CorruptDB’) WITH NO_INFOMSGS;
GO
Chọn các lệnh trong phần Try to access the Orders table, ấn F5 để truy vấn vào dbo.Orders table trong CorruptDB database không thể truy vấn vì có lỗi về về tính nhất quán logic.
— Try to access the Orders table
SELECT * FROM CorruptDB.dbo.Orders;
GO
Chọn các lệnh trong phần Access a specific order, ấn F5 để truy vấn đến một record được chỉ định truy vấn thành công, điều này chứng tỏ chỉ có một số trang trong database bị lỗi về tính nhất quán logic
— Access a specific order
SELECT * FROM CorruptDB.dbo.Orders WHERE OrderID = 10400;
GO
Chọn các lệnh trong phần Repair the database, ấn F5 để repair lại database (lưu ý: chỉ thực hiện lệnh này như là một chọn lựa cuối cùng khi không có bản backup nào trước đó).
— Repair the database
ALTER DATABASE CorruptDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB(‘CorruptDB’, REPAIR_ALLOW_DATA_LOSS);
GO
ALTER DATABASE CorruptDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO
Chọn các lệnh trong phần Access the Orders table, ấn F5 để truy vấn lại vào dbo.Orders table trong CorruptDB database. Truy vấn thành công vì các lỗi đã được sửa chữa.
— Access the Orders table
SELECT * FROM CorruptDB.dbo.Orders;
GO
Chọn các lệnh trong phần Check the internal database structure, ấn F5 để kiểm tra lại tính toàn vẹn của CorruptDB database. Lần này sẽ không có thông báo lỗi vì cấu trúc database đã được nhất quán
— Check the internal database structure
DBCC CHECKDB(‘CorruptDB’) WITH NO_INFOMSGS;
GO
Chọn các lệnh trong phần Check data loss, ấn F5 để kiểm tra dữ liệu bị mất. Lưu ý số record trong order details table không khớp với số record trong order table một số dữ liệu đã bị mất.
— Check data loss
SELECT DISTINCT OrderID
FROM CorruptDB.dbo.[order details] AS od
WHERE NOT EXISTS (SELECT 1
FROM CorruptDB.dbo.Orders AS o
WHERE o.orderid = od.orderid);
GO
B. Maintaining Indexes (bảo trì chỉ mục): Tại SSMS, mở file MaintainingIndexes.sql trong folder module 11, chọn các lệnh trong phần Create a table with a primary key, ấn F5 để tạo ra table dbo.Phonelog với primary key, mặc định sẽ tạo ra clustered index primary key field.
— Create a table with a primary key
USE AdventureWorks;
CREATE TABLE dbo.PhoneLog
(PhoneLogID int IDENTITY(1,1) PRIMARY KEY,
LogRecorded datetime2 NOT NULL,
PhoneNumberCalled nvarchar(100) NOT NULL,
CallDurationMs int NOT NULL
);
GO
Chọn các lệnh trong phần Insert some data into the table, ấn F5 để insert 10.000 dòng vào table này.
— Insert some data into the table
SET NOCOUNT ON;
DECLARE @Counter int = 0;
WHILE @Counter < 10000 BEGIN
INSERT dbo.PhoneLog (LogRecorded, PhoneNumberCalled, CallDurationMs)
VALUES(SYSDATETIME(),’999-9999′,CAST(RAND() * 1000 AS int));
SET @Counter += 1;
END;
GO
Chọn các lệnh trong phần Check fragmentation, ấn F5 kiểm tra phân mảnh. ở cửa sổ kết quả ghi chú giá trị ở hai cột avg_fragmentation_in_percent và avg_page_space_used_in_percent
— Check fragmentation
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(‘dbo.PhoneLog’),
NULL,NULL,
‘DETAILED’);
GO
Chọn các lệnh trong phần Modify the data in the table, ấn F5 update dữ liệu cho table.
— Modify the data in the table
SET NOCOUNT ON;
DECLARE @Counter int = 0;
WHILE @Counter < 10000 BEGIN
UPDATE dbo.PhoneLog SET PhoneNumberCalled = REPLICATE(‘9’,CAST(RAND() * 100 AS int))
WHERE PhoneLogID = @Counter % 10000;
IF @Counter % 100 = 0 PRINT @Counter;
SET @Counter += 1;
END;
GO
Chọn các lệnh trong phần Re-check fragmentation, ấn F5 kiểm tra lại phân mảnh, ở cửa sổ kết quả ghi chú lại giá trị ở hai cột avg_fragmentation_in_percent và avg_page_space_used_in_percent, lúc này đã bị thay đổi do data page đã bị phân mảnh.
— Re-check fragmentation
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(‘dbo.PhoneLog’),
NULL, NULL, ‘DETAILED’);
GO
Chọn các lệnh trong phần Rebuild the table and its indexes, ấn F5 để thực hiện rebuild lại index trên table này.
— Rebuild the table and its indexes
ALTER INDEX ALL ON dbo.PhoneLog REBUILD;
GO
Chọn các lệnh trong phần Check the fragmentation again, ấn F5 thực hiện kiểm tra phân mảnh lại lần nữa, ở cửa sổ kết quả ghi chú lại giá trị ở hai cột avg_fragmentation_in_percent và avg_page_space_used_in_percent lúc này đã giảm do không còn phân mảnh.
— Check the fragmentation again
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘dbo.PhoneLog’),
NULL, NULL,’DETAILED’);
GO
C. Tạo Maintenance Plan: Tạo lịch bảo trì database định kỳ.
Tại SSMS chọn server SQL, phải chuột vào Maintenance Plans chọn Maintenance Plans Wizard. Tại màn hình Maintenance Plan Wizard ấn next. Tại cửa sổ Select Plan Properties, trong phần name: đạt tên là Daily Maintenance, ấn nút change
Tại cửa sổ New Job Schedule, trong phần Name: nhập vào Daily, trong phần Occurs chọn daily, Occurs once at: nhập vào 3:00:00AM (thực hiện hàng ngày vào lúc 3 giờ sáng) OK
Trở lại cửa sổ Select Plan Properties ấn next, tại cửa sổ Select Maintenance Tasks chọn các checkbox Check Database Integrity, Reorganize Index, Update Statistics và Back up Database (Full) next
Tại cửa sổ Select Maintenance Task Order ấn next, Tại cửa sổ Define Database Check Integrity Task chọn AdventureWorks database OK Next
Tại cửa sổ Define Reorganize Index Task, trong phần database chọn AdventureWorks database Ok, trong phần Object chọn Tables and views next
Tại cửa sổ Define Update Statistics Task, trong phần Database chọn AdventureWorks database Ok, trong phần Object chọn Tables and views next,
Trong phần Define Backup database (Full) Task, chọn AdventureWorks database Ok, qua tab Destination chọn option Create a backup file for every database, trong phần folder nhập vào F:\Module 11\backup next
Tại cửa sổ Select Report Options, chọn checkbox Write a report to a text file, trong phần folder location: nhập vào F:\Module 11 Next, tại màn hình Complete the Wizard ấn Finish, chờ cho tiến trình hoàn tất ấn close.Ở cửa sổ Object Explorer, chọn Maintenance Plans, Phải chuột vào Daily Maintenanceand chọn Execute, chờ tiến trình kết thúc ấn close. (tiến trình này có thể mất vài phút)
Phải chuột vào Daily Maintenanceand chọn View History, tại cửa sổ Log File Viewer xem cột Task Name sẽ thấy các các tác vụ đã thực hiện Close
Vào đĩa F:\Module 11\backup sẽ thấy file backup của AdventureWorksdatabase đã được tạo, trong thư mục F:\Module 11 có file Daily Maintenance_Subplan_1_xxxxx.txt
Mr CTL – CTL.VN