Back up the transaction log for the database to free up some log space.

Guys,
I got this message and immediately backup the transaction log. It
fixed the problem.
We are using a third party (Legato) backup which is doing a full backup
of the sql. I am not too thrilled about this product.
Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
this problem? Do I still need to backup the transaction seperate?
Also, do I want to append to media or over write existing if I were to
backup the transaction only(I backed the server up daily, so the flat
files would go onto tape)?
I am on SQL 2000 SP4.
Thanks advance.
Tnttractng@.gmail.com wrote:
> Guys,
> I got this message and immediately backup the transaction log. It
> fixed the problem.
>
> We are using a third party (Legato) backup which is doing a full backup
> of the sql. I am not too thrilled about this product.
> Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
> this problem? Do I still need to backup the transaction seperate?
> Also, do I want to append to media or over write existing if I were to
> backup the transaction only(I backed the server up daily, so the flat
> files would go onto tape)?
> I am on SQL 2000 SP4.
>
> Thanks advance.
> Tnt
Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
database backups. Create a scheduled SQL Agent job for each. Don't
use third-party agents, like Legato, for direct database backups, they
tend to be awkward to restore from if necessary. Use the built-in
commands to dump the database (and log) backups to disk, then use
Legato to backup those disk files.|||Tracy,
Would backing up using the GUI be enough? Does the GUI "Full Backup"
take care of the log problem that I had stated earlier or do I have to
do the transaction log backup separate?
Still new to the backup command so I don't want to use it yet till I
test it.
Thanks,
tnt
Tracy McKibben wrote:
> tractng@.gmail.com wrote:
> > Guys,
> >
> > I got this message and immediately backup the transaction log. It
> > fixed the problem.
> >
> >
> > We are using a third party (Legato) backup which is doing a full backup
> > of the sql. I am not too thrilled about this product.
> >
> > Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
> > this problem? Do I still need to backup the transaction seperate?
> > Also, do I want to append to media or over write existing if I were to
> > backup the transaction only(I backed the server up daily, so the flat
> > files would go onto tape)?
> >
> > I am on SQL 2000 SP4.
> >
> >
> > Thanks advance.
> > Tnt
> Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
> database backups. Create a scheduled SQL Agent job for each. Don't
> use third-party agents, like Legato, for direct database backups, they
> tend to be awkward to restore from if necessary. Use the built-in
> commands to dump the database (and log) backups to disk, then use
> Legato to backup those disk files.|||tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
>
A full backup does not truncate the transaction log. I'm guessing,
since you're asking these questions, that you don't fully understand how
the transaction log is used. You have a couple of options:
1. Spend some time in Books Online, learning about how that mechanism
works, and how it protects you from data loss. Backing up the
transaction log at regular intervals gives you the ability to restore a
database to virtually any point in time. You first have to understand
how the log is used, and what to do with those backups if/when you need
them.
or
2. Put your databases into Simple mode, eliminating the need to
truncate the transaction log. You don't have to perform transaction log
backups, but you also have a higher risk of data loss.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
No. As Tracy suggested, if you don't understand backups you should
study the documentation carefully before you do anything. If you fail
to do that then your data may be at risk.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--