June 9, 2011 at 2:52 am
Hi,
I am thinking in Changing my SQL Server 2000 and 2005 database from simple recovery model to full recovery model.
It makes no sense that this databases are in simple recovery model.
Do i need to test my apps before changing afetr changing the recovery model?
I think that it is not necessary because it's just a change of recovery model, but still i would like your advice.
Thank you.
P.S ,
I have some bulk insert during the end of the day in this databases, i think that this was why the person that creted this databases let them in simple recovery model (for the log not to grow).
If i change to full recovery model but do regular transaction log backups the log will be at the same size as if it as in simple recovery model?
June 9, 2011 at 2:59 am
No testing, just make sure you manage the log.
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
June 9, 2011 at 3:02 am
Thank you Gail.
What about this:
"I have some bulk insert during the end of the day in this databases, i think that this was why the person that created this databases let them in simple recovery model (for the log not to grow).
If i change to full recovery model but do regular transaction log backups the log will be at the same size as if it as in simple recovery model?"
June 9, 2011 at 3:08 am
Probably not. In simple and bulk-logged, some operations are minimally logged. In full recovery they are fully logged.
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
June 9, 2011 at 3:15 am
hum.... In that case i have a problem.....
Maybe i have to swich between recovery models when doing the bulk insert (bulk verocry and then back to full again) but that will make me redo the app....
That's a problem...
Suppose that if i change to full recovery model, that the log will grow more that when i was in simple recovery model, because of the minimal logging.
Because this database is bigger in size (because of the trsansaction log), when i make a backup of it to sent it to through the internet will the transaction log size affect my backup size?
What i ask is :
In simple recovery model, log don't grow so much and when i make a backup the total size of the file backup is small.
If i use full recovery the total size of the backup will increase because the log file is bigger?
Thank you
June 9, 2011 at 3:22 am
river1 (6/9/2011)
Hi,P.S ,
I have some bulk insert during the end of the day in this databases, i think that this was why the person that creted this databases let them in simple recovery model (for the log not to grow).
If i change to full recovery model but do regular transaction log backups the log will be at the same size as if it as in simple recovery model?
How/When to choose correct recovery model and backup types
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 9, 2011 at 3:26 am
Nope the log file will grow in simple recovery model if the log file have open transaction.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 9, 2011 at 3:34 am
What i ask is :
In simple recovery model, log don't grow so much and when i make a backup the total size of the file backup is small.
In simlpe recovery model you can't perform log backup first.
If i use full recovery the total size of the backup will increase because the log file is bigger?
Both the mode the log file will grow if it has open tran.
I suggest you to read my post.
If your are using simple in case of any disaster there will be a data loss.
In bulk logged PTR is not possible.
Pls can you tell me how much data loss can you afford
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 9, 2011 at 3:34 am
sorry , didn't understood?
June 9, 2011 at 3:43 am
muthukkumaran Kaliyamoorthy (6/9/2011)
In bulk logged PTR is not possible.
Not true.
In bulk-logged recovery point in time recovery is possible unless there is a minimally logged operation within the log backup that covers the time you are trying to restore to.
River: As for size of backup. Maybe. Can't say for sure.
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
June 9, 2011 at 3:43 am
"In bulk logged PTR is not possible"
What is PTR?
No data can be lost, that's why i need do change the recovery model to full.
June 9, 2011 at 3:46 am
"River: As for size of backup. Maybe. Can't say for sure. "
That's another problem... Because we have to send the backup through internet to other places and the connections are very very slow....
June 9, 2011 at 3:48 am
I think that the solution to my problems maybe truncating the trsanaction log after making a full backup of the database at the end of the day.
What do you think?
June 9, 2011 at 3:49 am
river1 (6/9/2011)
"In bulk logged PTR is not possible"What is PTR?
No data can be lost, that's why i need do change the recovery model to full.
Point in time recovery.
Ok then do the log backup often.
Note: Bulk logged model is best for bulk operation it will improve the over all server performance. As already told PTR ony not possible if there is any bulk operation done to the log file.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 9, 2011 at 3:52 am
river1 (6/9/2011)
I think that the solution to my problems maybe truncating the trsanaction log after making a full backup of the database at the end of the day.What do you think?
No no
No data can be lost, that's why i need do change the recovery model to full.
You told no data loss.
There can be a data loss if you are truncating the log file in case of disastor.
Read the truncating the log file
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply