Re: Backups and Transaction Log file size
- From: "Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx>
- Date: Wed, 14 May 2008 11:38:35 -0400
Bill,
The purpose of the Transaction log is to ensure database consistency in the event of say a failure. When any DDL or DML is done the actions are first written to the transaction log. When then transaction is committed or rolled back those actions are then forced to disk immediately before the actual data changes are. This ensures that if in the middle of a tran the power dies, when it comes back on line it will have the necessary information to either roll forward or roll back the changes to get it to a consistent state. Since the actual data pages are written in a lazy fashion the log needs to be hardened to disk to enforce that consistent state. A FULL backup simply copies all the data pages to the backup file and does not (basically) do anything to the transaction log file itself. Log entries will keep filling up the log file until there is no more room at which time the log file needs to expand to allow the new transactions. The area in the log file that holds older committed trans cannot be reused until they have been backed up via a transaction log backup. Once the committed trans have been backed up the space can then be reused for new trans. An exception is if there is a long open running tran. Even though the entries in the log file have been backed up to disk the area can not be truncated and reused until that open tran is either rolled back or committed. You can check for long running open trans with the DBCC OPENTRAN() command. But a couple of key points. Backups (either FULL or log) do not shrink the file. You need to use DBCC SHRINKFILE for that and it should only be used when you have way too much extra free space in the file since free space is required for normal operation. And second the only thing that will allow the space for the committed trans to be truncated and reused is a LOG backup. Issuing a LOG backup once a day is almost useless since your FULL backup will do almost the same thing. Once every 15 minutes or so is what a typical system will do. That allows the file to stay at a reasonable size and you get maximum recoverability in the event you have a problem.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Bill Bradley" <BillBradley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:839381F3-81A5-4BEF-A96C-6E1FD45FEEC8@xxxxxxxxxxxxxxxx
I am slowly going insane trying to figure out backups.
From all that I read and understand, the purpose of the transaction log is
to save actions until committed to the actual db, and, a backup of both db
and log should committ things and the log would get smaller (or at least have
more empty space).
Instead, I have a log that is growing bigger and bigger, and, is much larger
(1.3 Gb vs. 370 MB) than the DB.
I am using SQL 2K5 Ent SP2 with latest patches and hotfix rollup. The
database Recovery Model is Full. Each morning I run a maintenance plan that
includes backup of the db file, then the transaction log file (I later added
a second backup of the db file right after the transaction log file backup,
as I was unsure of the correct sequence). Each day, both files steadily get
bigger, with backups doing nothing to decrease the size (or available
freespace of the log file).
What do I not understand, or, am doing wrong?
I wouldn't care about the size (not out of room or anything), but, the speed
of the application using the database files gets REAL slow, the bigger the
log gets.
Thanks.
.
- Follow-Ups:
- Re: Backups and Transaction Log file size
- From: Bill Bradley
- Re: Backups and Transaction Log file size
- References:
- Backups and Transaction Log file size
- From: Bill Bradley
- Backups and Transaction Log file size
- Prev by Date: How to log into SQL Server?
- Next by Date: Re: Backups and Transaction Log file size
- Previous by thread: Backups and Transaction Log file size
- Next by thread: Re: Backups and Transaction Log file size
- Index(es):
Relevant Pages
|
