Log file won't truncate on backup (no open transaction)

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Running dbcc opentran in the problem database yields no open transactions
running
dbcc sqlperf(logspace)
shows log file over 95% full (1.5 gig )
Then doing a FULL database backup doesn't truncate the log (still 95%
full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
Problem just started in last couple of weeks. Running ok for over a year in
SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
backup.
I am fairly sure that dropping the database and recreating it from a backup
will probably fix this. I have done a restore to a new database and the log
is empty.
Any thoughts on how to fix without dropping and restoring the database?
Any thoughts on what may have caused this?
TIA,
MikeAre you doing transaction log backups? These should empty the log.
Christian Smith
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>|||As Christian indicate. Full (DB) backup does not empty the transaction log.
Only transaction log backup does. Perhaps the db was in simple recovery mode
and Tivoli (or someone else) did set it to full recovery mode). Also, before
you did your first db backup, the log is in "auto-truncate mode".
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"M Searer" <nospam@.nospam.com> wrote in message
news:%23XlsKHY9DHA.2924@.tk2msftngp13.phx.gbl...
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Running dbcc opentran in the problem database yields no open transactions
> running
> dbcc sqlperf(logspace)
> shows log file over 95% full (1.5 gig )
> Then doing a FULL database backup doesn't truncate the log (still 95%
> full) - running dbcc sqlperf(logspace) again doesn't change the usage %.
> Problem just started in last couple of weeks. Running ok for over a year
in
> SQL 2000, and several years in SQL 7. Only change is the use of Tivoli
> backup.
> I am fairly sure that dropping the database and recreating it from a
backup
> will probably fix this. I have done a restore to a new database and the
log
> is empty.
> Any thoughts on how to fix without dropping and restoring the database?
> Any thoughts on what may have caused this?
> TIA,
> Mike
>
>