April 4, 2013 at 5:39 pm
I am intermediate in database admin. Please give me your solution for this issue.
Size of my db is growing about 5M every 2 weeks. Now its size is 118G. my db is in full recovery mode, and I do not have log backup.
Is this a good solution?
1- Perform a full backup
2- execute checkpoint
3-Perform full Backup
4- Perform transactional log backup
5- shrink log file type
6- schedule transactional log backup
Also, can I do these processes during users are connected ?
April 4, 2013 at 7:33 pm
mp5387 (4/4/2013)
I am intermediate in database admin. Please give me your solution for this issue.Size of my db is growing about 5M every 2 weeks. Now its size is 118G. my db is in full recovery mode, and I do not have log backup.
Is this a good solution?
1- Perform a full backup
2- execute checkpoint
3-Perform full Backup
4- Perform transactional log backup
5- shrink log file type
6- schedule transactional log backup
Also, can I do these processes during users are connected ?
Skip steps 2 and 3. Move step 6 to the new step 1.
And if your log file is truly too big then do step 5.
So the new order would be:
1. Create tlog backup schedule
2. Perform Full backup
3. Shrink log file if too large.
Doing this will allow the schedule to take care of the tlog backups.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2013 at 10:20 pm
mp5387 (4/4/2013)
I am intermediate in database admin. Please give me your solution for this issue.Size of my db is growing about 5M every 2 weeks. Now its size is 118G. my db is in full recovery mode, and I do not have log backup.
Is this a good solution?
1- Perform a full backup
2- execute checkpoint
3-Perform full Backup
4- Perform transactional log backup
5- shrink log file type
6- schedule transactional log backup
Also, can I do these processes during users are connected ?
I would say, do full backup weekly, daily differential and hourly log backups based on the RTO provided.
If your database is in full recovery model, then definitely, you need to do log backup to truncate the logs.
SueTons.
Regards,
SQLisAwe5oMe.
April 4, 2013 at 11:04 pm
SQLCrazyCertified (4/4/2013)
mp5387 (4/4/2013)
I am intermediate in database admin. Please give me your solution for this issue.Size of my db is growing about 5M every 2 weeks. Now its size is 118G. my db is in full recovery mode, and I do not have log backup.
Is this a good solution?
1- Perform a full backup
2- execute checkpoint
3-Perform full Backup
4- Perform transactional log backup
5- shrink log file type
6- schedule transactional log backup
Also, can I do these processes during users are connected ?
I would say, do full backup weekly, daily differential and hourly log backups based on the RTO provided.
If your database is in full recovery model, then definitely, you need to do log backup to truncate the logs.
SueTons.
An RTO was not provided. And your suggestion has more to do with RPO and not RTO. Though neither has been specified so they are both unknowns.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2013 at 1:47 am
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
April 5, 2013 at 1:44 pm
SQLRNNR (4/4/2013)
SQLCrazyCertified (4/4/2013)
mp5387 (4/4/2013)
I am intermediate in database admin. Please give me your solution for this issue.Size of my db is growing about 5M every 2 weeks. Now its size is 118G. my db is in full recovery mode, and I do not have log backup.
Is this a good solution?
1- Perform a full backup
2- execute checkpoint
3-Perform full Backup
4- Perform transactional log backup
5- shrink log file type
6- schedule transactional log backup
Also, can I do these processes during users are connected ?
I would say, do full backup weekly, daily differential and hourly log backups based on the RTO provided.
If your database is in full recovery model, then definitely, you need to do log backup to truncate the logs.
SueTons.
An RTO was not provided. And your suggestion has more to do with RPO and not RTO. Though neither has been specified so they are both unknowns.
Thanks for the correction, however, I thought RPO(Recovery Point Objective) and RTO(Recovery Time Objective) are basically same? Please let me know.
SueTons.
Regards,
SQLisAwe5oMe.
April 5, 2013 at 1:49 pm
SQLCrazyCertified (4/5/2013)
SQLRNNR (4/4/2013)
SQLCrazyCertified (4/4/2013)
mp5387 (4/4/2013)
I am intermediate in database admin. Please give me your solution for this issue.Size of my db is growing about 5M every 2 weeks. Now its size is 118G. my db is in full recovery mode, and I do not have log backup.
Is this a good solution?
1- Perform a full backup
2- execute checkpoint
3-Perform full Backup
4- Perform transactional log backup
5- shrink log file type
6- schedule transactional log backup
Also, can I do these processes during users are connected ?
I would say, do full backup weekly, daily differential and hourly log backups based on the RTO provided.
If your database is in full recovery model, then definitely, you need to do log backup to truncate the logs.
SueTons.
An RTO was not provided. And your suggestion has more to do with RPO and not RTO. Though neither has been specified so they are both unknowns.
Thanks for the correction, however, I thought RPO(Recovery Point Objective) and RTO(Recovery Time Objective) are basically same? Please let me know.
SueTons.
RPO, how close to the time of failure do you need to recover. Or, how much data are you willing to lose time wise. Can your people manually recover the last one hour of work for example.
RTO, how long it will take to restore operation.
April 5, 2013 at 1:58 pm
They can be radically different.
For example, I worked with a card processing company a few years back, their RTO was minutes. Literally, every minute they were down they were losing money because they couldn't process transactions. Their RPO however was 48 hours. They could lose up to 2 days of data without concern because they got summaries from the banks every 24 hours containing the previous 2 days of data, which they could use to recreate any lost transactions in their systems.
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
April 5, 2013 at 2:02 pm
Thanks guys.
SueTons.
Regards,
SQLisAwe5oMe.
April 8, 2013 at 11:24 am
Hi,
Thanks for your answer. I did this solution on my test server and it worked, but I am worry! Can I do that on my production server during the users are connected and they work with application?
Also, we donot have lots of transaction daily.So, I want to schedule one full backup daily, and one transactional backup every one hour. Now for providing transactional backup which method is good? append or overwrite
I appreciate your answer in advance!
April 8, 2013 at 11:43 am
mp5387 (4/8/2013)
Now for providing transactional backup which method is good? append or overwrite
Neither.
Overwrite would be rather silly with log backups since you need all log backups in sequence to restore. Set it to overwrite and you'll only have the last one, useless for restoring.
Append leaves all backups in the same file which means your file is growing larger and larger and anything goes wrong you lose the entire set of backups rather than one.
All backups to their own file, timestamp in the file name.
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
April 8, 2013 at 12:53 pm
Thanks for your prompt answer:-)
April 8, 2013 at 12:59 pm
GilaMonster (4/8/2013)
mp5387 (4/8/2013)
Now for providing transactional backup which method is good? append or overwriteNeither.
Overwrite would be rather silly with log backups since you need all log backups in sequence to restore. Set it to overwrite and you'll only have the last one, useless for restoring.
Append leaves all backups in the same file which means your file is growing larger and larger and anything goes wrong you lose the entire set of backups rather than one.
All backups to their own file, timestamp in the file name.
Also, create a clean up job to remove the old trans log backup files after taking a new full backup everyday.
SueTons.
Regards,
SQLisAwe5oMe.
April 8, 2013 at 1:01 pm
Please ignore.....sent in error.
SueTons.
Regards,
SQLisAwe5oMe.
April 8, 2013 at 1:07 pm
SQLCrazyCertified (4/8/2013)
GilaMonster (4/8/2013)
mp5387 (4/8/2013)
Now for providing transactional backup which method is good? append or overwriteNeither.
Overwrite would be rather silly with log backups since you need all log backups in sequence to restore. Set it to overwrite and you'll only have the last one, useless for restoring.
Append leaves all backups in the same file which means your file is growing larger and larger and anything goes wrong you lose the entire set of backups rather than one.
All backups to their own file, timestamp in the file name.
Also, create a clean up job to remove the old trans log backup files after taking a new full backup everyday.
SueTons.
I wouldn't recommend that. In fact, I'd say that's not a particularly good idea. What happens if that new full backup is damaged and won't restore? By deleting all the log backups from the previous day, you now have no way to recover.
I keep a full week of full and log backups if I can. At minimum 2 days worth.
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply