May 24, 2012 at 7:01 am
We have a production d/b that we back up to tape every night. This process sometimes fails so I wanted my own backups
I've set up 4 SQL Agent jobs that are now running: (I've checked the logs)
1. Backup my production d/b nightly
2. Backup its transaction logs hourly
3. Remove any .BAK file older than 3 days
4. Remove any .TRN file older than 3 days
So I'm seeing a .BAK file and a .TRN file being created every night at 2:00 am, and disappearing 3 days later. All is good there.
But the .TRN file is only created once a day, with the nightly BAK file at 2AM according to the date modified in Windows Explorer. Are my log backups working? The job log says they run for 3 to 7 seconds each hour, but I don't see the date modified change on an hourly basis.
I have the TRN log backup set to Append. Is that why I don't see the Date Modified change hourly?
Also, the size of my TRN files fluctuates wildly from day to day:
5/22: 473 Kb
5/23: 5.7 Gb
5/24: 32 Mb
Is this because my cleanup job is not actually deleting the transaction logs but just cleaning them out of older, unneeded data? Or should I be concerned about this?
Thanks in advance
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
May 24, 2012 at 7:16 am
I would expect a single transaction log backup when using APPEND, and I'd expect the transaction log backup to grow each hour, until it was replaced by a new backup file at 2.
_________________________________
seth delconte
http://sqlkeys.com
May 24, 2012 at 7:21 am
Okay, I'll monitor the .trn file sizes through the day. If I see it grow, then it's working.
Should the size fluctuation each day be of any concern?
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
May 24, 2012 at 7:28 am
Yes your log backups are working, you can do a query on the MSDB tables to check that they are working as well.
What you are doing is appending to an existing file the latest transaction log backup which is why you only see one file a day. I would personally change it to mark it with the actual date and time as well, this then will show you individual file backup sizes and also protect you against corruption, as should the single file with all logs become corrupt you wont be able to restore them.
select
bs.backup_set_id
,bs.database_name
,bs.type
,bs.backup_start_date
,bs.name
,bmf.physical_device_name
,bs.backup_size
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
order by bs.backup_start_date
As for the file growth sizes, that would indicate that one day you had little transactions resulting in small log backups and one day you had a few large periods of transactions resulting in a number of larger transaction log backups.
May 24, 2012 at 7:43 am
anthony.green (5/24/2012)
I would personally change it to mark it with the actual date and time as well, this then will show you individual file backup sizes and also protect you against corruption, as should the single file with all logs become corrupt you wont be able to restore them.
Anthony,
Thank you for those very clear answers and the script as well. This raises my comfort level quite a bit.
The daily TRN file is being datestamped in the name: (dbname)_backup_201205240206.trn so I have one TRN file per day.
Are you saying I should timestamp each hourly append to the TRN file? How would I do that?
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
May 24, 2012 at 7:52 am
You would need to build in a dynamic date call something like the below
declare @datestamp nvarchar(30), @cmd nvarchar(max)
set @datestamp = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR,CURRENT_TIMESTAMP,120),'-',''),':',''),' ','')
set @cmd = 'BACKUP LOG db1 TO DISK = C:\backups\db1'+@datestamp+'.trn'
select @cmd
exec (@cmd)
Take a look at the link in my signature to Ola's scripts, he does it better than I do and has a load more options and is fully cusomisable to your requirements.
May 24, 2012 at 8:20 am
Will do. Thanks again!
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply