Just a day before I received wonderful T-SQL Script from SQL Server Expert Imran Mohammed. What I am really impressed from this script is additional feature of changing recovery mode when SQL Server is enabling and disabling Index. When index are enabled changing SQL Server Recovery mode to simple makes whole operation faster. This stored procedure is self documented so I am not writing much about it.
I really want to encourage all blog readers and SQL Expert to actively participate like Imran Mohammed.
Stored Procedure USP_DisableEnableNonClusteredIndexes can be downloaded from here.
Execute SP using following script.
-- sample to Execute
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', 'N',1 -- DISABLE
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', 'N',2 -- REBUILD/ENABLE with out Changing Recovery model.
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', 'Y',2 -- REBUILD/ENABLE with Change Recovery model.
-- or
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS',NULL, 1 -- DISABLE
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', NULL ,2 -- REBUILD/ENABLE with out Changing Recovery model.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
What would be the ramifications of changing the recovery model of your database?
You would have a service level agreement with the business on a recovery strategy and changing your recovery model during maintenance tasks probably is not part of this agreement. Plus, how do you recover if the database goes down after you have switched the recovery model back and forth from full or bulk logged to simple and then back again?
Are you taking a full backup once the database is in simple mode – or what exactly would be your suggested approach of keeping integrity within the database and backups of the database in sync with what you are doing as well as keeping to the contract of your SLA?
I see this as a fun thing to do on a database that has no value such as adventureworks, but on a production database – processing many transactions per minute – with a service level agreement in place on a recovery strategy this is not going to sit well with the business that is paying for your services.
Good idea, but what about breaking the backup log chain.
http://msdn.microsoft.com/en-us/library/ms178052(SQL.90).aspx
@Simon Worth
I appreciate your response for the above article. I am not a genius. I love when experts like you comment on my work. I thank you for the same.
I will not defend myself, but would like to bring two points to your notice,
1. Script by default will not change recovery model, unless user specify an option to change it. It is just an added option.
2. This is only for Non-Clustered Index (excluding one created on Primary Keys and Unique Keys). Script was created to reduce execution time for huge data loads.
@Jason Crider
Thanks for comment, I completely agree with you.
I could have added Fill Factor as a parameter when rebuilding indexes, I could have used case function and removed many IF statements.
Regards,
IM.
good