SQL Scritp Fix Database

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 

-- Detach, re-attach database
ALTER DATABASE MyDatabase SET EMERGENCY;
EXEC sp_detach_db MyDatabase
EXEC sp_attach_single_file_db @DBName = MyDatabase, @physname = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MyDatabase.mdf'

2. Database Recovery Pending

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;

*Tham khảo danh sách các lỗi được hướng dẫn tại đây

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