Growing Log File - Shouldn''t the backup shrink it?
-
I have on my production server, a backup and maintenance plan to allow the database to be backed up to a file so the tape system can pick it up.
My log file seems to grow and not shrink. What can I do to prevent it from getting bloated.
I have a backup that runs Sunday, backing up the main DB to file (weekly). Every night, a backup runs that backs the DB up to a file (nightly), then backs up the transaction log.
I thought that the transaction log backup should decrease the size of the log file after it's backed up.
What am I missing?
-
You need to backup log more often, try to do it several times a day. It depends on frequency of modifications in your database.
-
Just to confirm, the backup run by the backup and maintance plan in the SQL Agent Jobs will shrink the log file.
Ours is large due to the number of transactions.
-
No, a backup will NOT shrink your transaction log file.
First, only a transaction log backup will affect the transaction log at all. A full backup will not make changes to the transaction log.
Second, a transaction log backup will not shrink a transaction log file. It will, however, reduce the utilized portion of the transaction log. A little terminology -
To remove inactive transactions from a transaction log is to TRUNCATE the log. This is done at the end of a transaction log backup.
To SHRINK a transaction log is to reduce the physical size of the file, and is done with the DBCC SHRINKDATABASE or DBCC SHRINKFILE command.
If your transaction log is continuing to grow, even though you are doing daily transaction log backups, you probably need to increase the frequency of your transaction log backups as Svetlana suggested. Many shops backup their transaction logs as often as every 15 minutes (including the shop that I work in). After you have the growth under control, then, based on the utilization of the log from day to day, you may be able to SHRINK the log to regain some space on your disk system. But don't do this without due research. Continually expanding and shrinking your transaction log is a tremendous performance hit. If a user commits a transaction just as the log begins to expand, that user may be sitting there for a while waiting for their transaction to complete!
Steve
-
I have a database that I backup every night and I understand the importance of doing a backup of the transaction log but what happens if the option is greyed out? Is there any way to enable it? The only options available right now is Complete and Differential. Both Transaction log and File and filegroup options are greyed out.
On another server I have I don't have that issue.
Thanks for any input.
-
Mike,
Take a look at your Recovery model. Its Simple, isn't it? In the Simple recovery model, transaction log backups are not possible because SQL truncates the transaction log at each checkpoint that is issued (behind the scenes). This used to be call Truncate Log on Checkpoint. See the terminology that I mentioned in a previous post in this thread. Obviously, if SQL throws away the transactions after applying them to the database, there is no point in backing up the TRANSACTION log! In some cases, this is fine. If the only updates that are occuring in your database are done through a batch job at night (like in a data warehouse), you don't need to keep the transactions. You can recover the database completely by either restoring the full backup or restoring the full backup and re-running the batch cycle.
If, on the other hand, your database is part of an OLTP system (OnLine Transaction Processing), where users are making constant updates to the database, Simple Recovery Model is not sufficient to recover the database, and you need to change to the Full Recovery Model. In Full Recovery, the transactions are only truncated from the transaction log when a transaction log backup is taken. This is the scenario you are referring to when you talk about the importance of backing up the transaction log.
Take a look at Recovery Models in Books Online to get a better understanding of these topics.
Steve
-
Hi to all,
I have the same problem too,
whice i ported few days ago. please i need your help, advice suggestions and comments.Thank you.
Regards,
Arnold
-
Arnold,
If the answer is not already in this thread, could you please be more specific about the problem that you are experiencing? I don't know how to help because I don't know exactly what the problem is.
Steve
-
Arnold,
I found the other thread. It was lurking right next to this one.
While a weekly full backup, daily diffs, and hourly tlog is an option, if possible, I would opt for the daily full. The diff adds complexity, and you need to keep the full and at least the most recent diff for recovery. With a daily full, once you have the new one, you can delete the previous (provided of course that it has been copied to tape).
The main thing that I would change in my backup strategy, is to take more frequent transaction log backups. Every 15minutes is not unreasonable.
Now, you also said that you are loading a large amount of data between 1am and 3am. Probably, that is where your log growth is coming from. If running log backups every 15 minutes still doesn't get it under control, you could consider a more drastic plan. You could change your recovery model to simple just before your batch run (1am), change back to full right after the batch run (3am), and take your daily full backup then. If you have a problem with the batch run, you can still recover, by restoring the previous night's full, all tlogs, and re-running your batch run. A problem any other time is handled by the most recent full, and all subsequent tlogs.
See if this helps, and if not, let us know!
Steve
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply