DBCC CHECKDB Database Corruption Recovery 

When database corruption has been discovered, the cleanest way of recovering is to restore your database from a backup and replay the transaction logs up to a point where you know the database to be healthy.  

If transaction log backups aren’t regularly taken or if data is constantly changing (or if you just don’t have any full backups that you can go back to), this will not be possible. 

Before going any further, take a backup of your database that you can revert back to (just in case something goes catastrophically wrong and leaves you in a worse state than you are already in 

There are two options in this circumstance. The first is to run a repair that cannot result in the loss of any data. This is done by running the following T-SQL command (substituting AdventureWorks with the name of your database but keeping the quotes) 

DBCC CHECKDB(‘AdventureWorks’, REPAIR_REBUILD) 

If this fails (which it will if there are any connections to your database, run this command first to set the database into single user mode. The ROLLBACK IMMEDIATE bit means that any transactions currently in progress will be cancelled and rolled back 

ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 

Now run the repair command again. Once complete. Run this to set the database back to Multi User mode 

ALTER DATABASE AdventureWorks SET MULTI_USER 

In the worst case, this will not be enough to fix your database corruption. Without a backup you can revert to, the only option is to allow SQL to try the repair regardless of whether it needs to lose any data. If you are happy with this then run the command below 

DBCC CHECKDB('yourDB', REPAIR_ALLOW_DATA_LOSS)

MSSQL Index Rebuild Simplified Guide

This guide is designed to do a full index rebuild and must be done out of hours as it will take the indexes offline meaning queries won’t be able to use them when searching for data 

• Create a new maintenance plan and call it something relevant like ‘IndexRebuild’ 
• In the maintenance plan, drag the task from the toolbox on the right ‘Rebuild Index Task’ into the white space in the bottom middle pane 
  
  
• Right click and edit this 

• Leave the connection as ’Local server connection’  
  
  
• Dropdown the Database(s) box and select the database/databases you want to rebuild the indexs for (choose the databases actually doing the work)
  
  
• Leave everything else as default 
  
  
• Click the calendar icon to the right of Subplan_1 – Not scheduled (On Demand) and select the time and frequency you want this to run 
  
  
• Now close this and save 
  
  

Note: You can run a reorganise task instead which is an online task meaning that it can be done whenever. This is the way to go if a system is heavily used 24/7. To set this up instead, just drag the Reorganise Index Task box into the maintenance plan instead. The settings are very similar, just set the databases you want to point it at.