January 13, 2005 at 10:09 am
Hi !
I need some better understanding about the full database back up and log file back up. I have one database. Main purpose of this database is to collect data (about 1500 to 2000 record per table every day).
I set up the complete database back up of tape drive about
I observe couple of days that my log file was getting too big around (1500 MB free space around 1200MB). I have to shrink log file every other day to shrink the file. To prevent the log file growth I am planning to set up the log file back 2 or 3 time a day (over write the same log file on hard drive) to prevent the growth of log file.
My question is what happens as soon as I do back up the log file only more often (Complete back up once a day) does it flush the committed data or I have to do full database back up first in order to flush the data from log file.
(Other way if I do only log file back up at 10 Am (log file was 250MB) does it flush the data and start filling from top or it keep growing from 251 MB until I do full database back up and then flush the log file and start from top) (In order to prevent the log file growth do I need to set up both complete (full) and log file back up more often or only log file back up 3 or 4 time can do that)
January 13, 2005 at 1:00 pm
If your not using the log file for recovery puposes than I would put the database into the simple recovery model and not worry about it.
If you need full recovery: the log file backup writes all commited transactions to the backup device. At that time the it will reuse the space in the log file. One reason that log files grow is based on the size of the transactions. If you have a large transaction the log file will continue to grow reguardless of the backup happening, because the transaction is not complete.
Also, if you need full recovery append every backup to the log file backup so you don't loose any transactions.
Hope this helps a little.
Tom
January 14, 2005 at 1:13 am
Your log full problem doesn't occur because of cross posting does it ?
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=155419
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=155416
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=155415
One message is usually enough to get an answer. Now I have to post this answer to all your threads....
Usually in a Production environment I backup logs every hour and do a full backup every day.
Logs record all the changes in the database that have occured. Backing up the log clears out the log, and then Sql server will reuse the space in the log file. If you don't backup the log file it will grow until you do back it up.
In order to restore a database, you have to restore a full backup first, then you can restore transaction log backups made after the full backup.
You need to have your transaction log backups APPEND otherwise they you won't be able to restore them. You could however initialise your tapes before the full backup. Obviously you need to rotate tapes for a complete backup plan, but that's a different issue.
As you said before, If you did a full backup every day at 12.00AM and hourly log backups... if the database was to die at 11:10, you could restore the full backup, then all the transaction log backups, which would bring you back to around 11:00.
Julian Kuiters
juliankuiters.id.au
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply