May 28, 2009 at 4:41 am
hi,
I have a maintenance plan that is backing up transactional logs every hour. What I have noticing is that the file size is small in KB. I am not sure if it is because the database is in use and that the file can't be verified, since I have checked the option to verify backup. Any ideas? Normally when I backup the transactional log manually the size is bigger.
May 28, 2009 at 5:28 am
🙂 size of the transaction log depends on the number of transactions logged every hour in your case .....
I dont think that there is any need to worry ...
Regards
Abhay
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
May 28, 2009 at 6:41 am
Is the automated job running more often than you used to run the backups manually? If so, that would explain the difference.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 28, 2009 at 6:46 am
Abhay,
So that means when I first did the backup manually it picked up all the trasacations?
May 28, 2009 at 6:48 am
Grant,
Yes the automated job is running every hour. I only did the manual backup once. I am getting a error in the log, which I will post in a bit. Thanks
May 28, 2009 at 6:53 am
stonecold560 (5/28/2009)
Abhay,So that means when I first did the backup manually it picked up all the trasacations?
Each transaction log backup gets all the logged operations to the previous transaction log backup and no more. So running it once an hour, each one contains a single hour's worth of log entries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 28, 2009 at 8:43 am
okay now it makes sense why the files are smaller sizes. Thanks for the clarification. Last question I have is if your backup expires after 14 days does that mean all the transactional backups older the 14 days can't be used? The way I have it setup now is it expires in 14 days and then it deletes it in a week. Does that sound reasonable?
May 28, 2009 at 10:27 am
If it expires, it usually means it can get overwritten or removed by the backup process, so you might want to watch for that. The real question is, how often are you running full backups? If you run them once a day, then you only need to keep transaction logs for the time you're interested in recovering, which is usually to the last full backup plus some amount of time. I suppose if you think you could recover to some point in time prior to the last full backup, and throw away all the data collected since that time (pretty unlikely in most businesses), then you might want to keep the log backups around longer. But in most circumstances, you only need them to the last full or the last incremental backup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 28, 2009 at 12:19 pm
I run full backup once a day at 11:00pm. So how would I setup an automatic job where it only backs up after 11:00pm job? Any ideas?
May 28, 2009 at 12:24 pm
You just schedule it to run every x number of minutes/hours, however often you or the the business have decided it needs to be run, and start it at 11:15 so that it starts after your backup. Pretty straight forward stuff.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply