how to truncate log

Hi there,
I use SQL 2000 and the log of a database is very big(I have full recovery).
I tried backing it up with Veritas BackupExec in order to truncate it, did
not work.
What should I do?
Thanks a lot, CalinRun DBCC OPENTRAN ('yourDB') to see whether you have an
active transaction that prevents the log from being
truncated.
Linchi
>--Original Message--
>Hi there,
>I use SQL 2000 and the log of a database is very big(I
have full recovery).
>I tried backing it up with Veritas BackupExec in order to
truncate it, did
>not work.
>What should I do?
>Thanks a lot, Calin
>.
>|||There is a difference between truncating the log and shrinking the file
size. A normal log backup will truncate the log, freeing internal space,
but the file size will remain the same. You'll need to use DBCC SHRINKFILE
to reduce the transaction log file size.
Jim
"Tester" <CalinGuga@.netscape.net> wrote in message
news:%23E8xJUkzDHA.2412@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> I use SQL 2000 and the log of a database is very big(I have full
recovery).
> I tried backing it up with Veritas BackupExec in order to truncate it, did
> not work.
> What should I do?
> Thanks a lot, Calin
>|||I don't know about Veritas BackupExec but I do have some
knowledge of SQLServer backup. When you do a full DB
backup using SQLServer backup, the transactions which are
in the log file and which have been marked as commited
are deleted from the log file. Then you can run DBCC
SHRINKFILE and it will work.
If you have Auto Shrink checked on the database
properties, then the SQLServer will shrink the size of
the log file over time after a full DB backup. This is
the advantage of using a maintenance plan backup in
SQLServer.
If you are in a super hurry to shrink the log file, do
the following:
1. Make sure you are not publishing using Transactional
replication.
2. Make sure there are no open transactions by running0
DBCC OPENTRAN.
3. Make sure there are no remote server links to this
database.
4. Make sure you know what maintenance plans are using
this database because you will probably have to rebuild
them.
5. Detach the database.
6. Manually delete the log file in Windows Explorer.
7. Attach the database which will build a new, 504 KB,
log file.
8. Rebuild your mainenance plans.
>--Original Message--
>Hi there,
>I use SQL 2000 and the log of a database is very big(I
have full recovery).
>I tried backing it up with Veritas BackupExec in order
to truncate it, did
>not work.
>What should I do?
>Thanks a lot, Calin
>.
>