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...
> >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
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:
>> <jheas...@.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
> 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:
> 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 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.
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:
> > 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.
The db is not enable for replication|||Ahh. Try the DBCC SHRINKFILE command. However, due to virtual log
segmentation internally, it may STILL not shrink. There is help available
on the web for forcing a tlog to be shrinkable. It involves writing dummy
records to the tlog to force it over to the next virtual log boundary.
An easier fix if you can do it is to simply detatch and reattach using
sp_detach_db and sp_attach_single_file_db (assuming you have single file
db). There is a sp_attach_db command too, but you need to record
appropriate information for each db file prior to detach. PLEASE study
this command pair and test first if you use it!!
--
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174966348.518536.144910@.y80g2000hsf.googlegroups.com...
> On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> 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 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.
> 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.
>|||Well, yesterday afternoon the tlog finally cleared on its own, five
days after beginning tlog backups. I don't know why it took so long,
perhaps someone has insight into that. As the day wore on, the file
also shrunk physically.
I much appreciate the replies. Tibor, the articled on shrinking db
files is very informative. Thanks again, all.|||On Mar 28, 8:32 am, jheas...@.salvagedirect.com wrote:
> Well, yesterday afternoon the tlog finally cleared on its own, five
> days after beginning tlog backups. I don't know why it took so long,
> perhaps someone has insight into that. As the day wore on, the file
> also shrunk physically.
> I much appreciate the replies. Tibor, the articled on shrinking db
> files is very informative. Thanks again, all.
The log file PHYSICALLY shrank as the day progressed? Do you have the
Autoshrink option enabled on that database? You should turn that
off. Shrinking the database file is a logged operation, so every time
autoshrink decides to shrink the database, you're going to get log
activity, which won't truncate until the shrink operation finishes.
That might explain what you were seeing before. Autoshrink should NOT
be used on a production database.|||On Mar 29, 9:18 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 28, 8:32 am, jheas...@.salvagedirect.com wrote:
> > Well, yesterday afternoon the tlog finally cleared on its own, five
> > days after beginning tlog backups. I don't know why it took so long,
> > perhaps someone has insight into that. As the day wore on, the file
> > also shrunk physically.
> > I much appreciate the replies. Tibor, the articled on shrinking db
> > files is very informative. Thanks again, all.
> The log file PHYSICALLY shrank as the day progressed? Do you have the
> Autoshrink option enabled on that database? You should turn that
> off. Shrinking the database file is a logged operation, so every time
> autoshrink decides to shrink the database, you're going to get log
> activity, which won't truncate until the shrink operation finishes.
> That might explain what you were seeing before. Autoshrink should NOT
> be used on a production database.
Yes, it is enabled. I called the hosting company to ask about it.
They said it's part of their standard procedure to enable Autoshrink
when they set up a SQL Server instance for a client. The tech I spoke
with did not know why, but said it was OK to disable it, which I did.
Thank you, Tracy.