Backups and Transaction Log file size

[Bill Bradley]

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.

[Andrew J. Kelly]

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" <BillB...@discussions.microsoft.com> wrote in message
news:839381F3-81A5-4BEF...@microsoft.com...

[Bill Bradley]

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]

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" <BillB...@discussions.microsoft.com> wrote in message

news:5C13A2B3-DFFA-4ED4...@microsoft.com...

[Bill Bradley]

Thanks, Andrew!

I had run DBCC OpenTran(), and, nothing was shown as holding things open. I
kinda think it might be corrupt, or, something, now...

I'm thinking of just changing it from Full to Simple, and, be done with it...

[Andrew J. Kelly]

Did you run it in the context of that db or in master? It needs to be in the
particular db. If it is a problem with open trans changing to simple won't
fix that. You can try stopping and restarting SQL Server as that will roll
back any open trans when it restarts.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Bill Bradley" <BillB...@discussions.microsoft.com> wrote in message

news:AACF1AB9-1A61-4486...@microsoft.com...