Index operation
Posted by Simon Cho on 04/11/2012
Index rebuild and Statistics update
http://weblogs.sqlteam.com/billg/archive/2011/02/08/sql-server-scripts-i-use.aspx
Logging issue
Version : SQL2008 R2
http://msdn.microsoft.com/en-us/library/ms191484%28v=sql.105%29.aspx
|
Index operation |
Full |
Bulk-logged |
Simple |
| ALTER INDEX REORGANIZE | Fully logged | Fully logged | Fully logged |
| ALTER INDEX REBUILD | Fully logged | Minimally logged | Minimally logged |
| CREATE INDEX | Fully logged | Minimally logged | Minimally logged |
| DBCC INDEXDEFRAG | Fully logged | Fully logged | Fully logged |
| DBCC DBREINDEX | Fully logged | Minimally logged | Minimally logged |
| DROP INDEX | Index page deallocation is fully logged; new heap rebuild, if applicable, is fully logged. | Index page deallocation is fully logged; new heap rebuild, if applicable, is minimally logged. | Index page deallocation is fully logged; new heap rebuild, if applicable, is minimally logged. |
You need to change Bulk-logged operation when starting the index rebuild.
Even if Bulk-logged, “Alter index reoranize” is fully logged. So, please change the schedule of Trasanction log backup frequently.
Index rebuild
http://sqlfool.com/2010/04/index-defrag-script-v4-0/
Simon's SQL 
Leave a Reply