?
Solved

alter index with simple recovery mode vs full recovery

Posted on 2011-10-30
7
Medium Priority
?
597 Views
Last Modified: 2012-05-12
Hi,
How long usually it takes if I run reindex on 400GB db size under simple recovery compare to full recovery ? currently with full recovery it takes a lot of space and always cause the reindex job terminated half way.

So If I change to simple mode-run reindex, after reindex complete change to full recovery mode, again what will be happens with our point in time recovery ? Let says reindex job run under simple mode takes 4 hours to complete 1PM-5PM? Can I roll forward the transaction log backup which I set back to full recovery mode at 6PM ?
0
Comment
Question by:motioneye
7 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 336 total points
ID: 37055223
DBCC INDEXDEFRAG And ALTER INDEX REORGANIZE will behave as full logged even if the recovery model is Bulk or Simple.

Rebuilding index on simple recovery mode is always faster as compared to Full recovery because of large transaction log activity, your transaction log will fill too quickly in full recovery model and expand (if auto growth is enabled) this makes performance problems.

You cannot perform a point in time recovery after changing the recovery mode, this is because when you change the recovery mode the transaction log chain will change and may cause log truncation. If log is truncated you cannot roll forward the transaction logs. Also if you change the recovery mode to simple you cannot do a transcation log backup.

0
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 332 total points
ID: 37055592
That "Fully Logged" when reorganizing indexes is misleading. It just means that every change is logged, as long as the transaction (the reorganization of the single non-clustered index, or of a clustered and all non-clustered indexes for a table, since the latter depend on the former) runs. Simple Recovery allows to reuse the claimed transaction log space, while Full does not, as the transactions need to be retained for rollforward recovery.
"Minimially Logged" in the link means that just the "command" itself is stored in the log, no data.
It might be a good idea to switch to Bulk Logged (so you are not breaking the transaction log chain), then start DBCC DBREINDEX or ALTER INDEX REBUILD, since those are minimally logged. After that switch back to Full Logged.
0
 
LVL 3

Assisted Solution

by:GSGDBA
GSGDBA earned 332 total points
ID: 37055666
How long usually it takes if I run reindex on 400GB db size under simple recovery compare to full recovery ?
It depends on the no.of indexes you created on the tables of a database.
Considering the present size of the db, it should take 8 - 10 Hours( considering huge no of indexes on the db)

currently with full recovery it takes a lot of space and always cause the reindex job terminated half way.
Yes, SQL Server Engine log each transactions in Full recovery mode. There are chances for the job to fail considering the Disk space, Memory utilization, Processor utilization, etc.

So If I change to simple mode-run reindex, after reindex complete change to full recovery mode, again what will be happens with our point in time recovery ?

It's good to change the recovery model of large databases to simple and run the maintenance jobs.
After completion of Reindexing database.
Set database to full.
Take full backup,
Differential ( Daily)
Tran Log ( as it was earlier).
This change will not affect your Point in time recovery of the database.


Let says reindex job run under simple mode takes 4 hours to complete 1PM-5PM? Can I roll forward the transaction log backup which I set back to full recovery mode at 6PM ?

Yes, you can.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 1000 total points
ID: 37055761
Reindexing entirely a 400GB depends on the fllowing factors:
> IO configuration: in RAID 5 the reindex will take longer
> CPU configuration: the more CPU the faster
> Online usage: if the operation is performed while users are still connected


Based on the above, time may vary from 2 hours to several days.I'd recommend taking a selective approach to reindexing based on index usage or on partitionning.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 1000 total points
ID: 37055774
In other words, recovery is only but one factor playing on reindexing time, and perhaps not the most determining one (assuming you provisioned your log space well).  
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 1000 total points
ID: 37055779
I also strongly recommend that you set the SORT_IN_TEMPDB to YES and MAXDOP to all the CPU's when recreating the index: that will speed up things.
0
 

Author Comment

by:motioneye
ID: 37060668

I also strongly recommend that you set the SORT_IN_TEMPDB to YES and MAXDOP to all the CPU's when recreating the index: that will speed up things.

Yes I have enable sort in Tempdb , ufortunately we run on Standard Edition so MAXDOP will not be an option to us.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question