Một số câu lệnh sử dụng t-sql để sửa lỗi database MS SQL SERVER
KIỂM TRA TRẠNG THÁI DATABASES
DECLARE @status INT
SELECT @status = status FROM sys.sysdatabases WHERE name = DB_NAME()
PRINT DB_NAME() + ' - ' + CONVERT(VARCHAR(20),@status)
IF ( (1 & @status) = 1 ) PRINT 'autoclose'
IF ( (2 & @status) = 2 ) PRINT '2 not sure'
IF ( (4 & @status) = 4 ) PRINT 'select into/bulkcopy'
IF ( (8 & @status) = 8 ) PRINT 'trunc. log on chkpt'
IF ( (16 & @status) = 16 ) PRINT 'torn page detection'
IF ( (32 & @status) = 32 ) PRINT 'loading'
IF ( (64 & @status) = 64 ) PRINT 'pre recovery'
IF ( (128 & @status) = 128 ) PRINT 'recovering'
IF ( (256 & @status) = 256 ) PRINT 'not recovered'
IF ( (512 & @status) = 512 ) PRINT 'offline'
IF ( (1024 & @status) = 1024 ) PRINT 'read only'
IF ( (2048 & @status) = 2048 ) PRINT 'dbo use only'
IF ( (4096 & @status) = 4096 ) PRINT 'single user'
IF ( (8192 & @status) = 8192 ) PRINT '8192 not sure'
IF ( (16384 & @status) = 16384 ) PRINT '16384 not sure'
IF ( (32768 & @status) = 32768 ) PRINT 'emergency mode'
IF ( (65536 & @status) = 65536 ) PRINT 'online'
IF ( (131072 & @status) = 131072 ) PRINT '131072 not sure'
IF ( (262144 & @status) = 262144 ) PRINT '262144 not sure'
IF ( (524288 & @status) = 524288 ) PRINT '524288 not sure'
IF ( (1048576 & @status) = 1048576 ) PRINT '1048576 not sure'
IF ( (2097152 & @status) = 2097152 ) PRINT '2097152 not sure'
IF ( (4194304 & @status) = 4194304 ) PRINT 'autoshrink'
IF ( (1073741824 & @status) = 1073741824 ) PRINT 'cleanly shutdown'
1. Database Restoring
Mô tả: Khôi phục file backup vào Database bị bị lỗi như Restoring...
Sửa lỗi 1: Chạy câu lệnh sau thay tham số @dbname = tên của database đang bị lỗi
Use master
go
RESTORE DATABASE @dbname WITH RECOVERY
Sửa lỗi 2: Cách thủ công cần backup 2 file .MDF và .LDF trước rồi thực hiện
- Stop SQL Server.
– Vô thư mục chứa DB, move 2 file MDF và LDF ra chỗ khác.
– Start lại SQL Server.
– New Database với tên và cấu hình y chang cái database vừa move ra.
– Stop SQL Server.
– Move lại 2 file MDF, LDF vừa move ra ngoài lúc nãy. Nhớ cho phép chép đè.
– Start lại SQL Server, mọi thứ chạy bình thường như chưa hề có cuộc chia ly
Mô tả: Dịch vụ SQL Server bị dừng đột ngột do mất điện hoặc lỗi hệ thống dẫn tới khi khôi phục lại các database bị treo ở trạng thái này.
Sửa lỗi: Thay đổi @DBname theo tên 01 database cần sửa lỗi
ALTER DATABASE [@DBName] SET EMERGENCY; -- Chuyển trạng thái db sang cấp cứu
GO
ALTER DATABASE [@DBName] set single_user -- Chuyển trạng thái db sang chỉ 1 user dùng
GO
DBCC CHECKDB ([@DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS; -- Kiểm tra và sửa lỗi database cho phép mất các bản ghi gây ra lỗi
GO
ALTER DATABASE [@DBName] set multi_user -- Chuyển trạng thái db sang nhều người sử dụng như ban đầu
GO
Sửa lỗi cho trường hợp chạy nhiều database cùng lúc
Declare @sql nvarchar(max)
DECLARE @db_name VARCHAR(200);
DECLARE @i INT = 1;
DECLARE @db_names TABLE (
id INT IDENTITY(1,1) PRIMARY KEY,
db_name VARCHAR(50) NOT NULL );
INSERT INTO @db_names
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB') /* Databases which needs to be excluded */
AND state <> 0 /* Include only the database which are online */
AND is_in_standby = 0 /* database is not read only for log shipping */
And RTRIM(LTRIM(state_desc)) like '%EMERGENCY%';
WHILE EXISTS (SELECT 1 from @db_names WHERE Id = @i)
BEGIN
SELECT @db_name = db_name from @db_names WHERE Id = @i;
PRINT '--Database Rebuild Started: ' + @db_name
----1. Set EMERGENCY
--set @sql ='ALTER DATABASE '+@db_name +' SET EMERGENCY;'
--print @sql
--exec(@sql)
----2. Set Single
set @sql ='ALTER DATABASE '+@db_name +' SET SINGLE_USER;'
print @sql
exec(@sql)
--3. REBUILD DATABASE
set @sql ='DBCC CHECKDB('+@db_name+',REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;'
print @sql
exec(@sql)
--4. Set MULTI_USER
set @sql ='ALTER DATABASE '+@db_name +' SET MULTI_USER;'
print @sql
exec(@sql)
SET @i = @i + 1;
END
GO
3. Database Suppect
Mô tả: Do SQL Server bị tắt đột ngột, mất điện cho máy chủ, ổ cứng chứa database không đủ dung lượng trống...
Sửa lỗi: thay Dbname bằng tên 01 database cần sửa lỗi
EXEC sp_resetstatus Dbname; -- Lệnh reset trạng thái database
ALTER DATABASE Dbname SET EMERGENCY -- Chuyển trạng thái database sang cấp cứu
DBCC checkdb(Dbname) -- Kiểm tra tình trạng database cố gắng sửa lỗi trước
ALTER DATABASE Dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- Chuyển sang chế độ 1 người dùng
--Chạy theo thứ tự sửa lỗi trước, khuyến nghị dùng lệnh 1,2 xong kiểm tra database ok chưa, nếu không được thì dùng lệnh 3
--Lệnh số 3 chạy có thể mất dữ liệu do chấp nhận xoá bản ghi lỗi
--1. Kiểm tra vật lý database và cố gắng sửa lỗi
DBCC CHECKDB WITH PHYSICAL_ONLY
--2. Kiểm tra database, sửa chữa đảm bảo không mất dữ liệu bản ghi
DBCC CHECKDB ('Dbname', REPAIR_REBUILD)
--3. Kiểm tra database, sửa chữa, cho phép mất dữ liệu vì xoá những bản ghi lỗi
DBCC CheckDB ('Dbname', REPAIR_ALLOW_DATA_LOSS)
-- Sửa xong cho phép nhiều người sử dụng.
ALTER DATABASE Dbname SET MULTI_USER
4. Database cannot be opened due to inaccessible files or insufficient memory or disk space
Mô tả: Lỗi này do database của SQL sau khi sửa lỗi nào trước đó đang bị trạng thái tắt ( Offline) cần phải bật lên để có thể sử dụng được.
Sửa lỗi: Chạy câu lệnh này:
ALTER DATABASE [YourDatabaseName] SET ONLINE;
5. Database is read only
Mô tả: Lỗi này thường xảy ra khi database đang sử dụng nhưng quyền thư mục trên file database bị thay đổi không có Full Control dẫn tới lỗi
Sửa lỗi: Chạy câu lệnh này
USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET READ_WRITE WITH NO_WAIT
GO
