Vào một ngày đẹp trời, bổng dưng bạn nhận thấy website của mình ngưng hoạt động (hoặc báo lỗi), sau một hồi kiểm tra hoặc liên hệ với nhà cung cấp Hosting thì được biết dung lượng Cloud Hosting/ Cloud Server đã bị đầy mà trong đó Database chiếm dung lượng đáng kể gây gián đoạn website.
Hay bạn tự nhận thấy Database của mình đã tăng dung lượng, trong khi dữ liệu của mình chỉ có vài MB (hoặc vài GB) nhưng LOG File lại chiếm đến tận hàng trăm GB. Vậy bạn phải làm sao để giải quyết vấn đề này?
Có rất nhiều cách để xử lý giảm dung lượng LOG của Database:
1. Detach DB, xóa file LOG, sau đó Attach lại Database. Với cách này bạn phải thực hiện thủ công và buộc phải gián đoạn các kết nối từ các ứng dụng đến Database của bạn, sẽ không khả thi nếu ứng dụng của bạn đòi hỏi tính liên tục.
2. Backup LOG với OPTION là TRUNCATE_ONLY hoặc NO_LOG. Tuy nhiên từ phiên bản SQL Server 2008 thì Option này đã lược bỏ.
3. Chúng tôi khuyến nghị bạn thực hiện theo cách sau đây, vừa thực hiện nhanh chóng, dễ dàng lại vừa đáp ứng được nhu cầu liên tục của ứng dụng. Đó là, SHRINK FILE.
Giả sử, bạn đang có các file database: MB_Data.MDF và file Log: MB_Log.LDF, bạn có thể Run đoạn query sau đây từ SQL Management Studio hoặc có thể tạo sẵn và Run 1 Stored Procedure.
USE MB;
GO
ALTER DATABASE MB SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (MB_Log, 1);
GO
ALTER DATABASE MB SET RECOVERY FULL;
GO
Bạn có thể hiểu phương pháp này theo cách giải thích sau :
Có 3 chế độ Recovery trong SQL Server: FULL, SIMPLE và BULK LOGGED. Chế độ mặc định là FULL. Bạn có thể vào phần Option của database, xem trong Recovery Model. Khi ở chế độ này, bất kì một transaction nào, kể cả khi đã commit cũng đều được lưu trong LOG, do đó có thể dựa vào những Transaction này để “quay lui (rollback)” DB về bất kì thời điểm nào. Vì thế với những DB có Transaction nhiều, DATA ít thì file LOG vẫn có thể rất lớn.
Đầu tiên SET RECOVERY của DB về SIMPLE, ở chế độ này sau khi Transaction được commit, sẽ tự động xóa. Do vậy File LOG của database ở chế độ này thường rất nhỏ.
Dùng DBCC SHRINKFILE để SHRINK file log xuống còn 1 Mb. Nếu không set Recovery về SIMPLE, thì sẽ ko thể xóa bỏ hết các Transaction đã được commit. SHRINKFILE chỉ thu dọn và sắp xếp và phân bố lại dữ liệu, bỏ các vùng trống để giải phóng bộ nhớ, chứ không phải xóa dữ liệu. Vì thế ở chế độ FULL, SHRINKFILE hầu như ko tác dụng, hoặc nếu có thì file LOG dung lượng giảm đi ko đáng kể.
Sau đó SET RECOVERY về lại FULL. Trên MSDN cũng khuyên nếu muốn Backup LOG, các bạn nên chuyển về chế độ SIMPLE, hơn là backup LOG với Truncate_Only và No_LOG.
- Vấn đề đặt ra
- Trong quá trình làm việc, có một lần sau sự cố cúp điện đột ngột, tôi khởi động lại máy và phát hiện Database của mình đã chuyển sang trạng thái Suspect.
- Tôi không thể thực hiện bất kỳ câu truy vấn gì với Database này, và bản Backup gần nhất được thực hiện cách đây đã mấy ngày, trong khi tôi lại muốn làm việc với dữ liệu mới nhất. Vậy làm thế nào giải quyết sự cố Database bị Suspect.
II. Giải pháp
- Câu trả lời là chuyển Database sang trạng thái khẩn cấp (Emergency State) và bắt đầu các thao tác sửa chữa Database.
- Trạng thái khẩn cấp được giới thiệu từ phiên bản SQL Server 2005. Nó hữu dụng khi ta phải làm việc với một Database đang bị SQL Server đánh dấu là không đáng tin cậy (Suspect).
- Trong quy trình giải quyết các sự cố đã xảy đến với Database, trạng thái khẩn cấp cung cấp một vài tính năng linh động cho ta làm việc với một Database bị hư hay bị đánh dấu là không đáng tin cậy. Khi một Database được đặt vào trạng thái khẩn cấp, có 3 thay đổi chính trong cấu hình của Database này:
- Database trở thành READ ONLY
- Chỉ cho các user cấp cao nhất (như sa) truy cập
- Không cho đăng nhập vào (vì nó đang ở trạng thái READ ONLY)
- Ở trạng thái khẩn cấp, ta có thể truy cập dữ liệu, và có thể Export dữ liệu sang một Database khác, hay ta cũng có thể chạy DBCC CHECKDB trên Database này để sửa các lỗi đang tồn tại.
III. Thực hành
- Để minh họa sự hữu ích của trạng thái khẩn cấp, ta cùng theo dõi bài thực hành sau đây, được thực hiện trên SQL Server 2008:
- Trước hết tôi tạo một Database và làm cho SQL Server đánh giá nó là không đáng tin cậy
–Tạo 1 Database tên là DBEmergency CREATE DATABASE DBEmergency GO USE DBEmergency GO –Tạo 1 Table tên là tblTest với 2 cột Number, Name CREATE TABLE tblTest (Number TINYINT, Name VARCHAR(15)) GO –Insert 5 dòng vào Table tblTest INSERT INTO tblTest (Number, Name) SELECT 1, ‘One’ UNION ALL SELECT 2, ‘Two’ UNION ALL SELECT 3, ‘Three’ UNION ALL SELECT 4, ‘Four’ UNION ALL SELECT 5, ‘Five’ GO
- Sau đó tôi làm tiếp các bước sau:
- Stop SQL Server Service
- Mở file ldf của Database DBEmergency bằng notepad, sửa một vài dòng và lưu, đóng file lại.
- Start SQL Server Service.
- Bây giờ ta thấy Database đã ở trạng thái không đáng tin cậy (Suspect). Ta cũng có thể dùng T-SQL để xác nhận điều này
SELECT DATABASEPROPERTYEX (‘DBEmergency’, ‘STATUS’) AS ‘DBStatus’
- Và ở trạng thái này, ta không thể thực hiện bất kỳ câu truy vấn nào.
- Tiếp theo tôi chuyển Database sang trạng thái Emergency
ALTER DATABASE DBEmergency SET EMERGENCY GO Ta sẽ thấy như sau:
- Bây giờ ta sẽ dùng lệnh DBCC CHECKDB với tùy chọn REPAIR_ALLOW_DATA_LOSS để sửa lỗi cho Database này. Khi ta dùng REPAIR_ALLOW_DATA_LOSS, dữ liệu hay những Index bị hư sẽ bị loại bỏ khỏi Database để bảo đảm tính nhất quán. Nếu như những vấn đề phát sinh là do sự cố liên quan đến Transaction Log File, quá trình này sẽ tạo lại log file, ta đang ở trong trường hợp này
- Chạy đoạn script sau:
–Để sửa lỗi Database, ta cần chuyển sang chế độ Single User ALTER DATABASE DBEmergency SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO –Bắt đầu sửa lỗi cho Database DBCC CHECKDB (DBEmergency, REPAIR_ALLOW_DATA_LOSS) GO –Thiết lập Database trở lại trạng thái bình thường ALTER DATABASE DBEmergency SET MULTI_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE DBEmergency SET ONLINE GO
- Như vậy là ta đã hoàn thành việc khắc phục một Database bị sự cố Suspect. Kết quả như sau:
- Mặc dù trạng thái khẩn cấp là một giải pháp tuyệt vời đưa Database của chúng ta trở lại trạng thái họat động bình thường, nhưng đây không phải cách tiếp cận cho Database lớn hơn. Điều quan trọng ta cần ghi nhớ là cần có một chiến lược hiệu quả cho việc Backup dữ liệu.
- Khi một Database đang ở trạng thái RESTORING, thì không thể chuyển sang trạng thái EMERGENCY, tương tự là các trạng thái OFFLINE, ONLINE.
Kiến thức bài viết được tham khảo từ mssqltips.com