February 25, 2010 at 1:33 am
During my index rebuild, some DB's log files grow really big. I have been advised to switch the recovery model to BULK_LOGGED before the rebuild, and then back to SIMPLE after the rebuild.
I was wondering if I could/should do the same with a DB that is in FULL recovery model, and where transaction log backups are made every 10 minutes?
Is it a good idea to switch recovery models like this?
Or even if I want to shrink the log file of such a DB - is it advisable to switch from Full to Simple to do a SHRINKFILE, and then back to Full again?
Any advice will be great, thanks.
February 25, 2010 at 6:25 am
I do not think for DB which is in Simple recoevery will help.
But you can do the same for Full recovery DB if you wish but make sure you rever back immediately after the index rebuild. And make sure that there are no Bulk_logged operation happened during this time. Else you will miss those transaction while recovery.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 25, 2010 at 6:28 am
For production environment it's not advisable to put change recovery model from Full to Simple to shrinkfile. If you do so you need to take Full backup ASAP.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 25, 2010 at 7:28 am
Casper101 (2/25/2010)
During my index rebuild, some DB's log files grow really big. I have been advised to switch the recovery model to BULK_LOGGED before the rebuild, and then back to SIMPLE after the rebuild
That's pretty pointless. In both simple and bulk-logged things like index rebuilds are minimally logged. It's only in full that they are fully logged.
It's a common practice to switch from full to bulk-logged for the duration of index rebuilds and back to full afterwards, but you gain nothing by going from simple to bulk-logged and back again.
Or even if I want to shrink the log file of such a DB - is it advisable to switch from Full to Simple to do a SHRINKFILE, and then back to Full again?
it's not advisable to shrink logs in the first place, doesn't matter what recovery model the DB is in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2010 at 3:00 pm
GilaMonster (2/25/2010)
Casper101 (2/25/2010)
During my index rebuild, some DB's log files grow really big. I have been advised to switch the recovery model to BULK_LOGGED before the rebuild, and then back to SIMPLE after the rebuildIt's a common practice to switch from full to bulk-logged for the duration of index rebuilds and back to full afterwards, but you gain nothing by going from simple to bulk-logged and back again.
Do you recommend this on a production environment. Just wanted to confirm with you as I face space issues sometimes on the log drive during maintenance operations.
M&M
February 26, 2010 at 12:26 am
mohammed moinudheen (2/25/2010)
Do you recommend this on a production environment. Just wanted to confirm with you as I face space issues sometimes on the log drive during maintenance operations.
Yes, providing the small risks of bulk-logged are acceptable (no point-in-time, no tail log backups if there were bulk operations since last log backup). See BoL for full details.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2010 at 2:03 am
Thanks Gail.
Some more add ons...
On production server is this a good practise to change the recovery mode to simple , ReBuild indexes and switch back to FULL.? Please confirm.
If yes on Prod database..!
Ques: How can we come across the same with the database which is in Log Shipping (Primary). As we plan every weekend ReBuild Indexes. Will the LS fails if the recovery models switch back again after rebuild ?
As the same i faced as well in the past (space issue). Can we apply the same for database which is in Log Shipping or Replication.
Please advice... Apologies if am redirecting the thread in wrong way...
-Win.
Cheers,
- Win.
" Have a great day "
February 26, 2010 at 2:08 am
winslet (2/26/2010)
On production server is this a good practise to change the recovery mode to simple , ReBuild indexes and switch back to FULL.? Please confirm.
No. Terrible practice. Switching to simple breaks the log chain and requires a full backup before log backups can be started again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2010 at 2:29 am
Thanks Gail.
Cheers,
- Win.
" Have a great day "
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply