June 9, 2011 at 3:57 am
If i truncate the log file AFTER doing a full backup , how can i lose data?
The full backup as all the data.
It's like this:
After changing my databases to full recovery mode my backup plan will be:
During the day (when people are working):
Log backups from 10 to 10 minutos.
At the end of the day (when people are not working):
1 - Full backup of the database
2 - Truncate the log.
In the next day, all over again:
During the day (when people are working):
Log backups from 10 to 10 minutos.
At the end of the day (when people are not working):
1 - Full backup of the database
2 - Truncate the log.
This way i don't loose data and the backups sent over the internet are small, correct?
June 9, 2011 at 3:59 am
GilaMonster (6/9/2011)
muthukkumaran Kaliyamoorthy (6/9/2011)
In bulk logged PTR is not possible.
Not true.
agreed gail. I missed that.
The drawback is point in time (PTR) recovery is not possible, when there is a bulk operation done to the transaction log file.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 9, 2011 at 4:04 am
If i truncate the log file AFTER doing a full backup , how can i lose data?
The full backup as all the data.
It's like this:
You can't perform the log backup once you run the truncate St untill the full back up done.
Please read the article which i already given.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 9, 2011 at 6:53 am
What if i leat the databases in Simple recovery model because of the bulk operation but do a diferencial backup from 10 to 10 minutos?
I can talk to them and see if they can affort to loose 10 minuts of data.
Maybe this is a good solution.
A question that i have is this:
Can differencial backups made during the day of work cause bad perfoemance to OLTP databases?
My databases have 4 GB of space. Every month they grow 50 MB.
June 9, 2011 at 6:55 am
I don't agree with you, when you say that if I truncate the log file i can loose data.
If i do a Full Backup immediatly after doind the truncate i will not loose any data.
At least that is what i think.
Am i rong?
June 9, 2011 at 7:08 am
What do you gain by that? Other than ensuring that if the full backup is damaged you have NO recovery path.
Please read through this - Managing Transaction Logs[/url]
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 8:34 am
My scenario is this:
I have this database (oltp database) where users insert data during the day.
Database don't grow more that 50 MB per month because of this.
At the end of the week they import data by using bulk insert which uses minimal logging because it is in Simple recovery model.
I will change the database to full recovery model but my log file will grow a lot.
This will happen because Bulk inserts 70 MB of data to my database end of each week.
Almost all the data will be deleted from the database after the bulk insert and this is way, in simple recovery model the database only grow 50 MB per month.
Now that i will put the database in the full recovery model, the log file will grow a lot. It will grow because of the insert of the data , will grow because of the bulk insert and will grow because of the delete of this bulk inserted data.
So, after readying you links, i still have a dought.
If i do regular log backups so that the log file don't grow in full recovery model, will my production enviroment loose performance?
Question is:
Making log backups in oltp enviroment from (e.g) 30 to 30 minutos will cause perfomence issues to users that are inserting , selecting and deleting data through the app?
June 9, 2011 at 8:42 am
river1 (6/9/2011)
I will change the database to full recovery model but my log file will grow a lot.This will happen because Bulk inserts 70 MB of data to my database end of each week.
So switch to bulk-logged recovery model for the duration of the bulk inserts and back to full afterwards.
Now that i will put the database in the full recovery model, the log file will grow a lot. It will grow because of the insert of the data , will grow because of the bulk insert and will grow because of the delete of this bulk inserted data.
I said it might grow. Not it will grow. You need to test that. Besides, we're talking about insignificant amounts of space here. 70MB? That's nothing, absolutely nothing. If you were worrying about 70GB I could understand it.
Making log backups in oltp enviroment from (e.g) 30 to 30 minutos will cause perfomence issues to users that are inserting , selecting and deleting data through the app?
Not unless your IO subsystem is completely inadequate. Besides, at 50MB a full backup will take seconds. A log backup less than that.
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 8:55 am
Ok, thank you.
So, in my case what would you do?
Would you let the databases stay in simple recovery model and make regular differencial backups or would you change it to Full recovery model?
This databases have 4GB of size.
June 9, 2011 at 9:19 am
Totally depends on the requirements. If there's a no-data-loss requirement then I would switch to full recovery, use bulk-logged if necessary for bulk inserts.
4GB is still a tiny DB. I have test databases bigger than that (and flash drives 4 times that size)
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 11:13 am
GilaMonster (6/9/2011)
What do you gain by that? Other than ensuring that if the full backup is damaged you have NO recovery path.
Exactly we had this experience.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 9, 2011 at 11:22 am
Also remember that even bulk operations are not minimally logged if the table has a clustered index and is not empty.
So changing recovery model will not change much in this case.
http://msdn.microsoft.com/en-us/library/ms190422.aspx
-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply