:::: MENU ::::

Change recovery model of SQL Srever database before index rebuild

  • May 26 / 2009
  • 1
Backup and Restore, dbDigger, Indexes, Performance Tunning and Optimization

Change recovery model of SQL Srever database before index rebuild

Index rebuild may produce a lot of transaction logs. Hence it may cause of heavily populate your log file. So it would be better option to change recovery model of that specific database to simple before index rebuild process in it. Once index rebuild process is finished database may again be reverted to recovery model full. Assuming that AdventureWorks is in single user mode, we may run following commands to change and revert back the recovery model of a database for index rebuild operation.

 
USE AdventureWorks  
 GO  
 -- Set recovery mode to simple before index rebuild  
 ALTER DATABASE AdventureWorks  
 SET recovery SIMPLE  
 GO  
 -- Begin index rebuild process for selected tables  
 DBCC DBREINDEX ("HumanResources.Employee",'',90)  
 GO  
 -- Set recovery model back to full  
 ALTER DATABASE AdventureWorks  
 SET recovery FULL  
 GO  

While implementing above mentioned procedure make sure to reattain your backup sequence as without a new full backup and later log backup you will not be able to recover to the point if required. The reason is that backup sequence of log becomes disturb while changing recovery model.

  • Atif Shehzad

    There is an important point to keep in mind that changing recovery model will disturb your log backup sequence. So you have to perform full backup and log back up later after rebuild completes and recovery model is set to full.

Consult us to explore the Databases. Contact us