August 13, 2009 at 3:04 pm
Morning
I need some help around backups. I have 2 questions
The set up
I am doing a back up our NAV DB which is set to “Full recovery mode” each night I am only doing a database back up not logs
During the day I am doing Hourly transactional backups of the log. The first back up each day of the Log is a huge file (bigger than the data file) then the sequential hourly backups drop down to a tiny size. It seems like the log file is not reducing when I do my nightly full back up of the DB file.
What am I doing wrong? I don’t want to shrink the log as I know this isn’t right. I thought doing a full back up release the log space
Another thing I’ve noticed is that the nightly data back up doesn’t seem to be growing. On looking at the properties of the DB it is spread across two files. Would the Back up only be backing up one of these files?
Cheers
Nigel
August 13, 2009 at 3:17 pm
Full backups and differential backups do not truncate the transaction log of committed transactions. This is only accomplished by the transaction log backup.
August 13, 2009 at 3:28 pm
The hourly back up is a Transactional log back up Type.
August 13, 2009 at 3:33 pm
When are the t-log backups run and when is the full backup run?
August 13, 2009 at 3:44 pm
here is what I have done for years to my SQL 2000 and SQL 2005 databases: full bk nightly; log bk every 15 minutes from 5 am to 9 pm; truncate the logs after last t-log bk then call the nightly full bk.
It takes care of the headcache.
August 13, 2009 at 3:52 pm
htt (8/13/2009)
here is what I have done for years to my SQL 2000 and SQL 2005 databases: full bk nightly; log bk every 15 minutes from 5 am to 9 pm; truncate the logs after last t-log bk then call the nightly full bk.It takes care of the headcache.
And the truncate breaks the backup log chain. If the full backup taken Tuesday night becomes corrupt, you can't go back to the full backup taken Monday night and still recover to the most current t-log on Wednesday.
August 13, 2009 at 4:06 pm
Full DB back up is done at 9pm
Then Transactional Log back up starts at 8am next morning. The first back up IE at 8am is around a Gig with the following hourly ones around 3 meg
Under the shrink file option the Log is showing allocated space of 1480Mb with 1467mb free space
I know that shrinking is not a good idea and thought that the back ups would automiaclly reduce the file size
Thanks
August 13, 2009 at 4:13 pm
Lynn Pettis (8/13/2009)
htt (8/13/2009)
here is what I have done for years to my SQL 2000 and SQL 2005 databases: full bk nightly; log bk every 15 minutes from 5 am to 9 pm; truncate the logs after last t-log bk then call the nightly full bk.It takes care of the headcache.
And the truncate breaks the backup log chain. If the full backup taken Tuesday night becomes corrupt, you can't go back to the full backup taken Monday night and still recover to the most current t-log on Wednesday.
well, the IF can change the world so this works for me 😛
August 13, 2009 at 4:23 pm
nigelc (8/13/2009)
Full DB back up is done at 9pmThen Transactional Log back up starts at 8am next morning. The first back up IE at 8am is around a Gig with the following hourly ones around 3 meg
Under the shrink file option the Log is showing allocated space of 1480Mb with 1467mb free space
I know that shrinking is not a good idea and thought that the back ups would automiaclly reduce the file size
Thanks
When is the last transaction log backup?
August 13, 2009 at 4:46 pm
htt (8/13/2009)
Lynn Pettis (8/13/2009)
htt (8/13/2009)
here is what I have done for years to my SQL 2000 and SQL 2005 databases: full bk nightly; log bk every 15 minutes from 5 am to 9 pm; truncate the logs after last t-log bk then call the nightly full bk.It takes care of the headcache.
And the truncate breaks the backup log chain. If the full backup taken Tuesday night becomes corrupt, you can't go back to the full backup taken Monday night and still recover to the most current t-log on Wednesday.
well, the IF can change the world so this works for me 😛
Until you get to SQL Server 2008 where the truncate option no longer works. Besides, why truncate the log right after you have backed it up? Doesn't do any good either...
And, in SQL Server 2005 and above - transaction log backups and full backups do not block so you can run them at the same time. No reason to stop backing them up and restarting them again later - just run them all the time.
Just so you understand why I think this is so important... A couple years ago we had a system crash while backups were running. Actually, we had several systems affected because it was a SAN outage. We had to restore the database systems. My systems were recovered to within 15 minutes of the outage, because I was running my transaction log backups during the full backups. The other system was only recovered to the last transaction log that was run - which was at 7pm the previous day because the person who setup the system didn't think it was important.
Now, you might think that nothing was done on the system after 7pm - however, about 6 months prior to this incident the offices that used that system implemented a second shift and were running operations through midnight every night. Needless to say, they were not happy about losing 5 hours worth of work.
IF may be a good enough for you - but I didn't lose my job that day, others weren't so lucky 🙂
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 13, 2009 at 4:48 pm
6pm
August 13, 2009 at 4:57 pm
So what is happening is that all activity (updates/deletes/inserts) that occurs between 6:00 PM and the following morning at 8:00 AM are being backed up to the t-log backup run at 8:00 AM. Theat is why is it so much larger than the others taken the rest of the day.
August 13, 2009 at 5:07 pm
That doesn't seem right as no one is working from 6pm onwards.
If i look at the log on the file server it is showing a size of 1.5gig (time here is 11am) and under the shrink option it is showing that 1.4gig is free.
I thought after reading about back ups that the back up plan that i'm running should be automically shrinking the log file down ??
Cheers
Nigel
August 13, 2009 at 5:37 pm
nigelc (8/13/2009)
That doesn't seem right as no one is working from 6pm onwards.If i look at the log on the file server it is showing a size of 1.5gig (time here is 11am) and under the shrink option it is showing that 1.4gig is free.
I thought after reading about back ups that the back up plan that i'm running should be automically shrinking the log file down ??
Cheers
Nigel
No, the only thing that will shrink a file is SHRINKFILE or SHRINKDATABASE - either of which are not something you want to have to do at all.
Remember, all transactions are recorded in the transaction log. That includes your index rebuilds, statistics updates, inserts, deletes, updates, batch loads, etc...
If your log file is 1.5 GB - I would leave it at that size and keep backing up the transaction log throughout the night. There really is no reason to stop backing it up - it does not interfere with any other processes and reduces the risk of additional data loss should you run into a situation that requires a restore. If you lose the database drives and have to resort to a restore - you want to have as much data backed up on a different set of drives, with those files backed up to tape and sent offsite for disaster recovery. The more data you have separated from the actual system, the more opportunities you have to recover with minimal data loss.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 13, 2009 at 5:50 pm
even though the log file is twice the size of the data File?
I thought a full back up atomically truncates the log file
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply