May 26, 2009 at 9:11 am
Hello Everyone,
I set up log shipping on 80 databases in one server. The database size is various. the biggest one is 25G for only two month data. And I need to archive the data just for keeping two month data there.
Maintainence Jobs like below:
DBCC checkdb in 1:00AM daily;
Database fulll backup in 2:00AM Daily;
Reorganize index in 4:00AM weekly;
Log backup every 15 minutes.
After running log shipping one week, I noticed after Reorganizing index, the biggese database log file increased from 1G to 30G. And after full backup, the log size is bigger than before full backup even though no any transactions happened.
My questions:
1. When Fulll backup happens, should I stop log backup? Currently, Log backup and Full backup overlap
2. Why log is bigger after fulll backup?
3. I can change the biggest database from full recovery mode to bulk-logged mode, but if index es are still reorganizing, does that prevent log growing so big?
4. When I monthly do a archive, delete millions of record from 4 tables, Should I stop log shipping and after that, resychonize it?
Welcome any tips and suggestions.
Thanks.
May 26, 2009 at 9:45 am
I'm not completely sure what you are doing here. When you say it's 25G of data, what does that mean? The log files? The total data size?
When a reorganize happens, it's a logged event. So it writes to the log, and that can be a lot of data. Shouldn't be 30x, but it depends on how often you do it.
Full backups and log backups are fine together. The way a full works is that it starts backing things up, and then it includes log records for things that happened during the backup. This is the roll forward that happens at the end of restore. Log backups all run in a line, so they are fine.
If you delete a lot of data every month, those deletes get log shipped over automatically.
May 26, 2009 at 9:55 am
Steve Jones - Editor (5/26/2009)
I'm not completely sure what you are doing here. When you say it's 25G of data, what does that mean? The log files? The total data size?.
The Data file size is 25G and log file size is 1G.
When a reorganize happens, it's a logged event. So it writes to the log, and that can be a lot of data. Shouldn't be 30x, but it depends on how often you do it.?.
I am doing index reorganize weekly. I am wondering if there is a way to prevent log increase so big but keep log shipping working.
Full backups and log backups are fine together. The way a full works is that it starts backing things up, and then it includes log records for things that happened during the backup. This is the roll forward that happens at the end of restore. Log backups all run in a line, so they are fine.
My concern is why the log file size increases after fulll bakcup even though no any transaction happen except full backup.
May 26, 2009 at 9:59 am
Hi Judy
Are this separate jobs, or its on a job that runs after the first once have completed
May 26, 2009 at 10:15 am
CrazyMan (5/26/2009)
Hi JudyAre this separate jobs, or its on a job that runs after the first once have completed
They are seperate jobs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply