March 17, 2010 at 8:35 am
I have a database (SQL 2005 Std) that has the Recovery Model set to full and all I do is a nightly Full backup (nothing else) and a weekly Index maintenance job. Is all my data sitting in the log file? And if so what’s the best practice for getting the log data committed to the database and controlling the size of the log file?
March 17, 2010 at 8:38 am
Right now, your log file is going to keep growing until it uses up all the available disk space. You really need to setup periodic (scheduled) transaction log backups to help control the growth of the transaction log.
May I suggest that you read the last article I reference below in my signature block? It is well worth your time.
March 17, 2010 at 9:11 am
Your data is committed to the data file. That happens shortly after a transaction completes. In full recovery, the inactive log records are not cleared until they are backed up.
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
March 17, 2010 at 9:16 am
Also, backing up your transaction logs will increase your disaster recovery options by allowing point-in-time recovery.
March 17, 2010 at 1:13 pm
So would it be safe to say that each night when this Full Mode database is backed up it's backed up in its entirety including the data in the log file. This would mean that even though the log file has been backed up or committed to the database (mdf file) it will still remain the size it was before the backup? For example: if I run a full backup and at the time the log file is 200MB the size of the log file will remain 200MB. Would I then run a Shrink command on that database?
March 17, 2010 at 1:23 pm
Read the last article I have referenced below in my signature block.
You really need to schedule periodic transaction log backups through out the day. This will help you control the growth of your transaction log and provide you with the ability to restore to a point in time should you need it.
March 17, 2010 at 2:05 pm
Lynn Pettis (3/17/2010)
Read the last article I have referenced below in my signature block.You really need to schedule periodic transaction log backups through out the day. This will help you control the growth of your transaction log and provide you with the ability to restore to a point in time should you need it.
This.
For example:
Full backup at midnight
Transaction log backup every 1/2 hour starting at 1 am - 11:30 pm
If your system crashes at 4:45 pm, you can now recover by restoring the full backup, then restoring your transaction log backup up until 4:30 pm, and possibly up to 4:45 if you can get a tail log backup.
If you only do a full backup, with no transaction log backups, when the system crashes, you will have to tell your boss that all transactions for the day since last midnight are lost.
Shrinking causes fragmentation and is not needed if you do regular backups or have emergency disk space issues.
March 17, 2010 at 3:12 pm
shusta (3/17/2010)
I have a database (SQL 2005 Std) that has the Recovery Model set to full and all I do is a nightly Full backup (nothing else) and a weekly Index maintenance job. Is all my data sitting in the log file? And if so what’s the best practice for getting the log data committed to the database and controlling the size of the log file?
If there aren't any Transaction log backups set up, would you mind if you can tell us why you have your database in full recovery model..??
A mere full recovery model doesn't guarantee a point-in-time restore as long as you take the transaction log backups.
Thanks...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
March 18, 2010 at 4:22 am
shusta (3/17/2010)
So would it be safe to say that each night when this Full Mode database is backed up it's backed up in its entirety including the data in the log file.
No, it is not safe to say that at all.
A database in FULL Mode means nothing unless you've implemented a plan to take advantage of that Recovery Mode. A FULL database backup (NOT the same thing as FULL Recovery Mode) only backs up the data file and JUST ENOUGH of the transaction log to bring the database to a consistent state once restored. It does NOT backup the transaction log as a whole.
If you're getting confused on the issue, the best thing to assume is that FULL backups do NOT backup up the data in the log. (Forget about the "just enough" transactions comment, because apparently it's causing comprehension problems).
Transaction Log backups back up only the transaction log.
This would mean that even though the log file has been backed up or committed to the database (mdf file) it will still remain the size it was before the backup? For example: if I run a full backup and at the time the log file is 200MB the size of the log file will remain 200MB. Would I then run a Shrink command on that database?
Don't go this route. It will only cause you much headaches. The log file will not "remain" 200MB. It will grow beyond that every moment you haven't backed it up. Shrinking the log file manually will not resolve your issue and is a bad habit to get into. If you really, really, really don't want to backup the log file, switch to SIMPLE recovery mode. That will solve your problem.
Otherwise, start a regular Transaction Log backup. It's good practice and good CYA. Also, read the links on Lynn's .sig file and read about backups in Books Online. They will help your understanding of the issues involved.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply