Lab SQL 2014 P10 -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