Log backups not truncating log

I just took over administration of a SS 2000 database that is hosted
by a third party service. There was a full backup being run each
night, with no transaction log backups. The TL did not truncate after
I started log backups. There are no uncommitted transactions in the
log according to DBCC OPENTRAN. I don't have access to the machine
itself, only to SQL Server via Enterprise Manager. Please respond if
you have any suggestions. Thank you.<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>
Not truncating, or not shrinking?
Log backups don't shrink the size of the data file.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>
> Not truncating, or not shrinking?
> Log backups don't shrink the size of the data file.
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
Not truncating. There is 450 MB of used space after the most recent
TL backup.|||What does you virtual log file layout say (see about mind section of
http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Also, check for old
open transactions (DBCC
OPENTRAN).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jheasley@.salvagedirect.com> wrote in message
news:1174930061.164826.99350@.n76g2000hsh.googlegroups.com...
> On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
> Not truncating. There is 450 MB of used space after the most recent
> TL backup.
>|||What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
What percentage is used?
As said, a log backup does not shrink the file. To shrink the file,
you'll need to run a DBCC SHRINKFILE against the log.|||On Mar 26, 6:36 pm, "cstrong" <clive.str...@.googlemail.com> wrote:
> What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
> What percentage is used?
> As said, a log backup does not shrink the file. To shrink the file,
> you'll need to run a DBCC SHRINKFILE against the log.
I understand the backup does not shrink the file physically, but
should truncate the transactions that have been written to the data
file. My file is not truncating after the backup, so it keeps growing
physically. Here are the results of the DBCC commands:
DBCC OPENTRAN:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SQLPERF(LOGSPACE)
Database Name: MyDb
Log Size (MB): 474.4922
Log Space Used (%): 99.84307
Status: 0
DBCC LOGINFO
This returns 1878 rows. The status column has a value of 2 for every
row, which would seem to indicate every VLF is in use.
Thanks for the replies.|||Full backups do not remove transactions from the transaction log. If you
are not backing up the tlog, it will grow indefinitely. You can either
start backing it up (the tlog), or you can issue the BACKUP log mydatabase
with truncate_only command to flush out the committed transactions. ONLY do
this if you no longer care about using the tlog for recovery purposes!
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>|||On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Full backups do not remove transactions from the transaction log. If you
> are not backing up the tlog, it will grow indefinitely. You can either
> start backing it up (the tlog), or you can issue the BACKUP log mydatabase
> with truncate_only command to flush out the committed transactions. ONLY
do
> this if you no longer care about using the tlog for recovery purposes!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>
I understand that full backups do not remove transactions from the
TL. My problem is the tlog file is not being truncated after the tlog
backup runs.|||On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
> I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
Is the database enabled for replication? Transactions won't truncate
from the log if they are awaiting replication.|||On Mar 27, 8:45 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
>
> Is the database enabled for replication? Transactions won't truncate
> from the log if they are awaiting replication.
The db is not enable for replication