| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2009-01-05 : 14:02:52
|
| Hello,From time to time we rebuild indexes. Before rebuilding indexes, I modify the recovery plan of the database from FULL to BULK_LOGGED (to prevent transaction log growth). After changing it back, I always backup up the transaction log.What I noticed is that at this time it always takes ~ 30 minutes to back up the transaction log, whereas usually it takes just seconds. Executing DBCC SQLPERF(LOGSPACE) I saw that the transaction log grew to about 300 MB (as compared to usual 50 MB), so I could expect the transaction log backup to take a few minutes, but why 30 minutes? Does anyone have an explanation for this?Does anything happen internally when the recovery model is changed from full to bulk_logged and to full again, that could cause this increase in time for the transaction log backup?Thank you! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-05 : 19:32:30
|
| Yes, when you change recovery model from Full to Bulk-Logged,Bulk-operation like (bulk-insert,Reindexing)won't be logged in your transaction log (LDF)files so you will see minimum growth in it.After switching to Full Recovery model when you backup transaction log after Bulk operation,Your log backup captures everything so you will be able to restore point in time. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2009-01-06 : 10:33:54
|
| It was my mistake. The transaction log backup is actually 9.3GB in size after it took 30 minutes to complete. So I guess what was said above makes sense then.I have 2 more questions:1. In this scenario I backed up the transaction log after I switched to Full Recovery Model. If I would back up while still in the Bulk Logged Mode, what would change (size-wise, and recoverability-wise?)2. "sodeep" stated that the given scenario allows for point in time recovery, but from BOL it seems that we cannot do point in time recovery in this scenario - we can only recovery to the end of the transaction log backup. Is my understanding of BOL correct?Thank you! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-06 : 12:18:19
|
quote:
Originally posted by sodeep
quote:
Originally posted by sql_er
It was my mistake. The transaction log backup is actually 9.3GB in size after it took 30 minutes to complete. So I guess what was said above makes sense then.I have 2 more questions:1. In this scenario I backed up the transaction log after I switched to Full Recovery Model. If I would back up while still in the Bulk Logged Mode, what would change (size-wise, and recoverability-wise?)If you backup log in Bulk-logged recovery model,when you restore database,it will still be Bulk-logged recovery model and can't recover point in time.As mentioned above,your backup size will be huge.2. "sodeep" stated that the given scenario allows for point in time recovery, but from BOL it seems that we cannot do point in time recovery in this scenario - we can only recovery to the end of the transaction log backup. Is my understanding of BOL correct?As I said,You won't be able to recover in point when you backup log in Bulk-logged Rec model but after you change to Full Recovery model,when you backup log ,you can recover point in time. You should read BOL Carefully.Thank you!
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
powersakthi
Starting Member
9 Posts |
Posted - 2009-05-04 : 07:07:29
|
quote:
As I said,You won't be able to recover in point when you backup log in Bulk-logged Rec model but after you change to Full Recovery model,when you backup log ,you can recover point in time. You should read BOL Carefully.
Hi sodeep, As sql_er mentioned, Even I'm not able to locate in BOL that after switching to FULL from BULK and taking a TLOG Backup will help to do Point-in-Time recovery. I tried this in my setup but got this message "This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time." Could you please give more information on whether you are trying to me UPTO THE POINT or POINT-IN-TIME Recovery?//SakthiMy Blog - http://www.sqlserverdba.co.ccMy Home - http://wtv.watchtechvideos.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
powersakthi
Starting Member
9 Posts |
Posted - 2009-05-04 : 12:55:28
|
| So for exampleTRANSACTION 1 : update names where set name='sakthi' where name=1TRANSACTION 2 : update names where set name='bakthi' where name=2FULL - BULK-LOGGED SWITCHTRANSACTION 3 : SELECT * INTO cityname from statenameBULK-LOGGED - FULL SWITCHPERFORM LOG BACKUPTRANSACTION 4 : update names where set name='makthi' where name=3Here I can restore to point till 1 or 2 or 4. Is my understanding correct? I will surely test it out but want to understand theoritically correct.//SakthiMy Blog - http://www.sqlserverdba.co.ccMy Home - http://wtv.watchtechvideos.com |
|
|
|
|
After recovery model change trans backup take long