Re: Backups and Transaction Log file size
- From: "Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx>
- Date: Wed, 14 May 2008 17:48:43 -0400
It sounds like a classic case of a long running open transaction. If some one or something started a tran 10 days ago and never committed it you can not reuse that portion of the log so it will just grow and grow. If you run DBCC OPENTRAN() in the context of that db it should tell you if this is the case or not. Find the client and either commit or roll it back. If you know it is a garbage connection you can kill the SPID and it will roll back any changes that the SPID may have open and allow you to backup and truncate properly.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Bill Bradley" <BillBradley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:5C13A2B3-DFFA-4ED4-AC9E-7368E561580B@xxxxxxxxxxxxxxxx
Andrew,
What you say is what I thought I understood (although I didn't understand
that a FULL Backup didn't do the Log file, too).
However...in my case, I'm using a product to scan and deploy for patches,
Shavlik NetChk Protect, and, do two scans and deploys daily to 2500 computers
(to catch new ones and those that were off). This produces a heathy amount
of log file size, and, it was NOT getting smaller, but, actually got up to
9.5 GB (database size was < 500 MB at its largest), even though I was doing
daily Full backups (and...backups were successful).
When I attempted to shrink the log file, there was only 25 MB free, so...the
logs were not being committed or truncated.
Even after I added Transaction Log backups, there remained only 50 MB free,
out of 9 GB.
I ran sys.databases dump, and the columns about transaction file truncation
were 2 and waiting for backup.
So...even though I was backing up both files, the logs, which SHOULD have
gotten if not smaller, at least with more free space, were not changing.
And...for whatever reason, the size of the log file causes a HUGE response
slowdown in the product--making it almost unusable.
Thanks.
"Andrew J. Kelly" wrote:
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
- Re: Backups and Transaction Log file size
- From: Andrew J. Kelly
- Re: Backups and Transaction Log file size
- From: Bill Bradley
- Backups and Transaction Log file size
- Prev by Date: Re: Not on the C: Volume Please
- Next by Date: Re: Backups and Transaction Log file size
- Previous by thread: Re: Backups and Transaction Log file size
- Next by thread: Re: Backups and Transaction Log file size
- Index(es):
Relevant Pages
|
|
