Backups & Transaction Files

6 Jul 2009 2:18 PM

PsyberFox

Hi,

I am having some issues with my transaction files on SQL 2005.

I am running a full backup every evening and I expect my transaction file to
be cleared to the data file. However, this is not happening and my
transaction file keeps on filling up. I have no idea how to clear this to my
data file. I don't want to autogrow my files and they have more than
sufficient space to handle the data coming through, but the only problem is
that I keep on having to increase my files due to the transaction log being
filled up.

Is there a setting I'm missing on the backup command, or can someone please
assist me otherwise?!

Thank you kindly,

6 Jul 2009 2:24 PM

Uri Dimant

Hi
No, FULL database backuo DOES NOT clear the log file. You will have BACKUP
LOG file or set up recovery mode of the database to SIMPLE instead of FULL
(like in your case , am I right)

Show quoteHide quote

"PsyberFox" <Psyber***@discussions.microsoft.com> wrote in message
news:1F8D374E-4145-4695-A7C8-F8D076FAEC50@microsoft.com...
> Hi,
>
> I am having some issues with my transaction files on SQL 2005.
>
> I am running a full backup every evening and I expect my transaction file
> to
> be cleared to the data file. However, this is not happening and my
> transaction file keeps on filling up. I have no idea how to clear this to
> my
> data file. I don't want to autogrow my files and they have more than
> sufficient space to handle the data coming through, but the only problem
> is
> that I keep on having to increase my files due to the transaction log
> being
> filled up.
>
> Is there a setting I'm missing on the backup command, or can someone
> please
> assist me otherwise?!
>
> Thank you kindly,

6 Jul 2009 2:53 PM

PsyberFox

Hi,
You are 100% correct... I am running a full backup as well as a transaction
log backup tonight... so let's see.
Thank you for the advice!
Rgds,
W

Show quoteHide quote

"Uri Dimant" wrote:

> Hi
> No, FULL database backuo DOES NOT clear the log file. You will have BACKUP
> LOG file or set up recovery mode of the database to SIMPLE instead of FULL
> (like in your case , am I right)
>
>
>
> "PsyberFox" <Psyber***@discussions.microsoft.com> wrote in message
> news:1F8D374E-4145-4695-A7C8-F8D076FAEC50@microsoft.com...
> > Hi,
> >
> > I am having some issues with my transaction files on SQL 2005.
> >
> > I am running a full backup every evening and I expect my transaction file
> > to
> > be cleared to the data file. However, this is not happening and my
> > transaction file keeps on filling up. I have no idea how to clear this to
> > my
> > data file. I don't want to autogrow my files and they have more than
> > sufficient space to handle the data coming through, but the only problem
> > is
> > that I keep on having to increase my files due to the transaction log
> > being
> > filled up.
> >
> > Is there a setting I'm missing on the backup command, or can someone
> > please
> > assist me otherwise?!
> >
> > Thank you kindly,
>
>
>

7 Jul 2009 6:30 AM

grbihno

If you have a database in the FULL recovery model, a big transaction load, do not backup log regulary
and it is set to autogrow, log will grow until it fills up your hdd.

If your logs grew, they will not shrink on they own no mater that one log backup.
Once you do a log backup it will clear unused part of the log, but it stays as big as it was before the backup.
If you log is to big, you're gonna have to shrink it.

you can see wich part of the log you are using, and wich part is free by using

DBCC LOGINFO('databasename')

Status of 2 means that this VLF is in use and 0 means that it's not.

(some more info http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-More-on-the-circular-nature-of-the-log.aspx)

You can than do log backup and DBCC SHRINKFILE file until you move active part of the log to the begining and achieve the size you want.
Size will probably be around what you were aiming for, not the exact number. This is due to the nature of VLF creation.

Show quoteHide quote

>Hi,
>You are 100% correct... I am running a full backup as well as a transaction
>log backup tonight... so let's see.
>Thank you for the advice!
>Rgds,
>W
>
>"Uri Dimant" wrote:
>
>> Hi
>> No, FULL database backuo DOES NOT clear the log file. You will have BACKUP
>> LOG file or set up recovery mode of the database to SIMPLE instead of FULL
>> (like in your case , am I right)
>>
>>
>>
>> "PsyberFox" <Psyber***@discussions.microsoft.com> wrote in message
>> news:1F8D374E-4145-4695-A7C8-F8D076FAEC50@microsoft.com...
>> > Hi,
>> >
>> > I am having some issues with my transaction files on SQL 2005.
>> >
>> > I am running a full backup every evening and I expect my transaction file
>> > to
>> > be cleared to the data file. However, this is not happening and my
>> > transaction file keeps on filling up. I have no idea how to clear this to
>> > my
>> > data file. I don't want to autogrow my files and they have more than
>> > sufficient space to handle the data coming through, but the only problem
>> > is
>> > that I keep on having to increase my files due to the transaction log
>> > being
>> > filled up.
>> >
>> > Is there a setting I'm missing on the backup command, or can someone
>> > please
>> > assist me otherwise?!
>> >
>> > Thank you kindly,
>>
>>
>>
>
>

6 Jul 2009 2:25 PM

Russell Fields

Sorry, but a full backup does not clear the transaction file. You must
also backup the logs separately.

BACKUP DATABASE ....
BACKUP LOG ...

The BACKUP LOG frees up transaction log space for reuse by future
transactions. The LOG backups need to be independent of the DATABASE backups
in order to provide a more complete recovery path for restoring databases.

(But this is a common misconception.)

RLF

Show quoteHide quote

"PsyberFox" <Psyber***@discussions.microsoft.com> wrote in message
news:1F8D374E-4145-4695-A7C8-F8D076FAEC50@microsoft.com...
> Hi,
>
> I am having some issues with my transaction files on SQL 2005.
>
> I am running a full backup every evening and I expect my transaction file
> to
> be cleared to the data file. However, this is not happening and my
> transaction file keeps on filling up. I have no idea how to clear this to
> my
> data file. I don't want to autogrow my files and they have more than
> sufficient space to handle the data coming through, but the only problem
> is
> that I keep on having to increase my files due to the transaction log
> being
> filled up.
>
> Is there a setting I'm missing on the backup command, or can someone
> please
> assist me otherwise?!
>
> Thank you kindly,