Re: Backups and Transaction Log file size

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" <BillBradley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:AACF1AB9-1A61-4486-9B19-41DFCC062610@xxxxxxxxxxxxxxxx
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" wrote:

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.
>>
>>



.

Relevant Pages

  • Re: Backups and Transaction Log file size
    ... It sounds like a classic case of a long running open transaction. ... 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. ... Once the committed trans have been ...
    (microsoft.public.sqlserver.setup)
  • Re: Backups and Transaction Log file size
    ... The purpose of the Transaction log is to ensure database consistency in the event of say a failure. ... A FULL backup simply copies all the data pages to the backup file and does not do anything to the transaction log file itself. ... 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. ... and log should committ things and the log would get smaller (or at least have ...
    (microsoft.public.sqlserver.setup)
  • Re: Backups and Transaction Log file size
    ... Find the client and either commit or roll it back. ... that a FULL Backup didn't do the Log file, ... Even after I added Transaction Log backups, ... Once the committed trans have ...
    (microsoft.public.sqlserver.setup)
  • Re: Backups and Transaction Log file size
    ... that a FULL Backup didn't do the Log file, ... Even after I added Transaction Log backups, there remained only 50 MB free, ... Once the committed trans have been ... and log should committ things and the log would get smaller (or at least ...
    (microsoft.public.sqlserver.setup)
  • Re: Still dont understand log backups
    ... The transaction log, whether or not it is one or many files, is a serial, ... until the end of the physical log file is reached. ... backup is executed, the active "virtual" logs are backed up, but the ... Using BULK LOGGED, does the online log file contain EVERY transaction for all time -- given that I don't truncate it manually? ...
    (microsoft.public.sqlserver.server)