August 11, 2008 at 4:38 am
Hi All
I am having a database, where we every night takes a full backup. The database has run for some time now and we have discovered that the log file is not truncated.
I tried to make another backup and then shrink the database and it did not help. Then I looked through my SQL book and discovered that the database is only truncated when a logfile backup is made.
My question is now, how do I design a backup scheme? We do not need a hourly backup of the log files and actually we only need the full backup. So should I after I made my fullbackup also make a log file backup, so the log files is truncated?
Hope you have some ideas
regards
Bo
August 11, 2008 at 4:46 am
bdh (8/11/2008)
Hi AllI am having a database, where we every night takes a full backup. The database has run for some time now and we have discovered that the log file is not truncated.
I tried to make another backup and then shrink the database and it did not help. Then I looked through my SQL book and discovered that the database is only truncated when a logfile backup is made.
My question is now, how do I design a backup scheme? We do not need a hourly backup of the log files and actually we only need the full backup. So should I after I made my fullbackup also make a log file backup, so the log files is truncated?
Hope you have some ideas
regards
Bo
Hi Bo,
You say that "actually we only need the full backup". If it is enough for you to recover to the last nights full backup, and you do not need any further recovery granularity, you may want to switch to simple recovery mode. (although I'd just set up the log backups and stay in full recovery mode)
Regards,
Andras
August 11, 2008 at 5:14 am
Hi - are you *sure* you don't need transaction log backups? If this is an OLTP database, this would mean if you have a problem during the day, you have no way of restoring to a point in time just before whatever caused the failure - meaning you have lost all data from the point of your last full backup. Even if your transaction load is low, a point in time restore from the backup and logs is going to be faster and a lot less hassle then re-keying. If - however, this is for example a reporting database updated once a day - then you can set up the database in Simple Recovery mode and configure the backup to run once your dataload has completed see http://msdn.microsoft.com/en-us/library/aa173563.aspx
hth
Andrew
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
August 11, 2008 at 5:15 am
How much data loss is acceptable if a drive fails or the database becomes corrupt?
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
August 11, 2008 at 5:57 am
Very business oriented Gail? Aggree with all though have found (rightly or wrongly that there is more then one way to skin a 't-log').
If you set yourself a task, like once a week, either ooh or at low peak (if you have either), take a full backup of the database, then set recovery to simple and then manually shrink the log.
Then just switch back to full recovery and dont loose the backup for a bit.
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 11, 2008 at 6:02 am
Adam Zacks (8/11/2008)
Very business oriented Gail? Aggree with all though have found (rightly or wrongly that there is more then one way to skin a 't-log').If you set yourself a task, like once a week, either ooh or at low peak (if you have either), take a full backup of the database, then set recovery to simple and then manually shrink the log.
Then just switch back to full recovery and dont loose the backup for a bit.
And take a full backup after you have truncated the log and switched back to full recovery mode 🙂 Full recovery mode is not activated until a full backup has been taken!
Regards,
Andras
August 11, 2008 at 6:07 am
Andras Belokosztolszki (8/11/2008)
And take a full backup after you have truncated the log and switched back to full recovery mode 🙂 Full recovery mode is not activated until a full backup has been taken!
Regards,
Andras
And a link for further reading: http://msdn.microsoft.com/en-us/library/ms178052.aspx
Regards,
Andras
August 11, 2008 at 6:08 am
Ooops, yeah that would have been a problem. Soz
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 11, 2008 at 6:10 am
Adam Zacks (8/11/2008)
Very business oriented Gail?
Acceptable data loss is one of the main things that determines whether or not log backups are necessary and how frequent they should be. If 'acceptable' data loss is 15 minutes, then daily full backups and simple recovery mode isn't going to cut it.
It's one of the things that doesn't get mentioned often. I'm sure it's no fun telling the big boss that 2 days of data has been lost because the log backups weren't set up properly. I certainly wouldn't want to be in that position.
If you set yourself a task, like once a week, either ooh or at low peak (if you have either), take a full backup of the database, then set recovery to simple and then manually shrink the log.
Then just switch back to full recovery and dont loose the backup for a bit.
Why do you want to shrink the transaction log file weekly? All you're doing is forcing it to grow again, increasing the number of virtual log files (results on slower backups), causing file level fragmentation and bringing activity in the DB to a halt as the log file grows.
Oh, and you need a full backup after changing back to full recovery, otherwise your log chain is broken. You will not be able to do point-in-time recovery after switching to simple until you take another full/diff backup.
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
August 11, 2008 at 6:14 am
Andras Belokosztolszki (8/11/2008)
And a link for further reading: http://msdn.microsoft.com/en-us/library/ms178052.aspx
Regards,
Andras
Or my semi-rant on the matter - http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/
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
August 11, 2008 at 6:21 am
I have worked with some systems before where there is barely enough disk to run the db's and logs and backup the db (locally) before deleting the previous nights backup. Obviously the backups then go to tape.
I was talking about a work-around that i have used in the past to keep low tansactional, low resource systems running. I would say from details in this topic it sounds like this system may fit that 'box'.
Havent ever had a bad crash or corruption on an OLTP database yet, so probably cant appreceiate the pain.
But i bow my head to experience and will be quiet. Oh and Gail, I was playing... I love reading your responses with people and was just poking fun, thats all. Wasnt being nasty :'(
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 11, 2008 at 6:27 am
GilaMonster (8/11/2008)
Adam Zacks (8/11/2008)
Very business oriented Gail?You will not be able to do point-in-time recovery after switching to simple until you take another full/diff backup.
Is that for the db, log or both? Thought it was the db but now doubting myself :S
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 11, 2008 at 6:31 am
Adam Zacks (8/11/2008)
I have worked with some systems before where there is barely enough disk to run the db's and logs and backup the db (locally) before deleting the previous nights backup. Obviously the backups then go to tape.
I helped out on one of those once, except the tape backup wasn't working.
http://sqlinthewild.co.za/index.php/2008/03/06/on-recoverability/
They're still picking up the pieces of that one.
But i bow my head to experience and will be quiet. Oh and Gail, I was playing... I love reading your responses with people and was just poking fun, thats all. Wasnt being nasty :'(
May I suggest more smilies? Tone is very hard to read in a post. 😉
And please don't be quiet. That defeats the purpose of a forum.
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
August 11, 2008 at 6:32 am
Adam Zacks (8/11/2008)
Is that for the db, log or both? Thought it was the db but now doubting myself :S
I don't quite understand the question. Elaborate more please?
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
August 11, 2008 at 7:38 am
when you change back to 'full' rec-mode from 'simple', should you then bakup the db and log (seperately) or just the db? I have only backed up the db when i have done my 'shrinkage trick' 😉
And on the other line, if you want more smilies, then you shall have them :hehe:
Hows it feel to have a cyber-geek-fan? 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply