Issues faced with REPAIR_ALLOW_DATA_LOSS

One day my manager came me to and asked me that there is a errors when we run integrity checks of database . We need to fix that through REPAIR_ALLOW_DATA_LOSS option. So what i did was I have taken downtime and ran the below query very immediately. Then database has gone to Single User mode and stuck there and immediately thrown a DEADLOCK ERROR I would have run this command without GO statement.

So I thought I would bring the database to  MULTI_USER at least, even that is not happening and showing the same DEADLOCK error again and again, then I have run the 2nd 3rd queries to bring the database to normal stage and re run the script to mitigate consistency errors. But DONT RUN the first below query with GO command in the middle of each sentence.


USE master;
ALTER DATABASE [DatbaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB ('DatabaseName', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE [DatabseName] SET MULTI_USER;

So run the below command if you want come out of DEADLOCK error and wants to address our main task which is run repair allow data loss

USE master
GO
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses 
WHERE dbid = db_id('DatbaseNameHere')
EXEC(@kill);
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE DatabseName SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Once you come out of DEADLOCK ERROR our main job is to address check page level errors. So run the below command without GO statement

USE master;
ALTER DATABASE DatabaseNameHere
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ('DatabaseNameHere', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
ALTER DATABASE DatabaseNameHere
SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server