Microsoft SQL Server is one of the most popular database, which is the largest used by organizations. Some time, we face database corruption issue and more than 95% of DB corruption happens due to hardware failure but some other reason could be responsible for database corruption.
- Bugs in SQL server
- Improper storage
- Unexpected system shutdown while the database is opened
- Changes in SQL account
- Virus Attack
Tips to repair corrupted database
It’s a very panic situation to deal with corrupted databases in SQL Server. Here is one solution to repair corrupted database but not with guarantee.
Step1:- Check database through command and analyze whole report
Step2:- Analyze the Table name, which has corruption of page / index or row.
dbcc checktable(‘Table name’)
if any corruption result generated on check table then run below command
Step3:- Convert DB into Single user mode
ALTER DATABASE “db name” SET SINGLE_USER WITH ROLLBACK IMMEDIATE
run below command
dbcc checktable(‘table name’), REPAIR_REBUILD)
If not resolved with step 2 then process step4
Step4:- execute update usage command
Note: – But update usage command is very risky command and it’s avoidable, make this command in test environment first then try to implement on production setup.
dbcc updateusage(‘Table Name’)
Update usage command clear all pages and allocation of index and table and reallocate it in disk, this command SHOULD be run only one time per TABLE / DATABASE.
Step4:- if all above 3 command not works, it’s happen only data corruption situation (5% chances) then last option is to REPAIR THE TABLE with ALLOW DATA LOSS. Please run below command, But make the table backup in any EXCEL / another table or any another TMP table before this command and Also verify COUNT level of row before implement and after implementation, if both are same then it’s OK otherwise you will get the data loss on corrupted row
dbcc checktable(“table name”, REPAIR_ALLOW_DATA_LOSS)
You Must Also Read