Question on Transaction Log Backups

[DataPro]

New to Sql Server, running SQL Server 2000.

Our transaction log file backups occasionally fail as the size of the
transaction log gets really huge. We'd like to schedule additional
transaction log backups. Does that require an exclusive on the database
or can the db be used during a transaction log backup?

Also, does switching to a bulk mode recovery model before a bulk
operation then switching back to full recovery mode after present any
issues?

Thanks in advance.

[Erland Sommarskog]

DataPro (da*******@yaho o.com) writes:

Here is what the vendor is recommending:

1. Back up db and log via Windows Explorer or normal db backups
2. Stop services (application... EAS)
3. Disconnect/detach database
4. Delete log via Windows Explorer
5. Reconnect/reattach database.....lo g will dynamically be allocated

Does this make sense?
No! Never delete a log file, unless you are told so by a Microsoft
support professional. There are so many people who have lost their
database, because they deleted the log. I guess it's safe if you
detach first, but I would like to take a gamble with a production
database.
They also want autoshrink turned on but its my understanding some SQL
DBA's don't want that. Your thoughts?

Shrinking a database file only make sense if there has been some
exceptional activity. Running a schema upgrade certainly counts as one.
But doing it as a matter of routine, or with autoshrink, only serves
to degrade the performance of the application in three different
ways:

1) The shrinking takes resources.
2) The autogrow takes resources.
3) The log file becomes fragmented on disk.

Autogrow is a mixed blessing. Up to SQL Server 6.5 there was no autogrow,
which meant that if the data or log segment was filled up, it was a
hard stop. Autogrow that was added in SQL 7 avoids these situations,
as long as the disk has space. However, for the DBA there is not
really any difference: he should should still monitor database size
amd increase the size in due time. A 10% autogrow in a 200 GB database
is very painfully noticeable fot the users.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

DataPro (da*******@yaho o.com) writes:

Well SQL Server definetely seems to be a different animal.

Here are some questions a newguy like myself would have about the
logging.

The documentation says that once a backup log truncate only is done, a
full backup should be taken as the recovery ability of the system has
been degraded. Why is that, if that command merely archives committed
transactions?
Up-to-the point recovery. You take a full backup at midnight, then
you backup the transaction log once an hour. at 10:23 you run an
UPDATE statement, but forget the WHERE clause. At this point you
can back up the transaction log.

You can the restore the full backup and reapply the transaction log
dumps but stop at 10:22, so you can recover the lost data. If you
somewhere along the line has backed up the transaction log with
NO_LOG or TRUNCATE_ONLY, the log chain is broken, and it's not
possible to continue to apply logs.
A SELECT @@TRANCOUNT yields a count of zero.
dbcc opentran(eas) says zero also.
dbcc loginfo yeilds 308 rows marked with a '2' which apparently implies
that they are open transactions. Why don't they show up in trancount
then?

If a log backup supposedly makes the log 'reusable' then why would a 12
gig log grow another Gig right within a few hours of the backup with
minimal database use?
Wouldn't most of that space be reusable.

I'm not really sure what you are talking about here. If the log file
grew, apparently there was some action going on in the database.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[DataPro]

Thanks for your replies.
In regards to:

"> A SELECT @@TRANCOUNT yields a count of zero.

dbcc opentran(eas) says zero also.
dbcc loginfo yeilds 308 rows marked with a '2' which apparently implies
that they are open transactions. Why don't they show up in trancount
then? If a log backup supposedly makes the log 'reusable' then why would a 12
gig log grow another Gig right within a few hours of the backup with
minimal database use?
Wouldn't most of that space be reusable.

I'm not really sure what you are talking about here. If the log file
grew, apparently there was some action going on in the database. "

1.) What I'm not clear on is this....a dbcc loginfo yeilds 308 rows
marked with a 2.
Are those 'active' uncommitted transactions? What do those rows
represent?

2.) If backing up a log makes the space within a log file 'reusable'
why did the log grow in size if the space in the log was made reusable?
Wouldn't the minimal activity I referenced use that reclaimed space
within the log instead of making it bigger?

3) And finally what is the relationship between the results of a dbcc
loginfo command, a dbcc opentran and a A SELECT @@TRANCOUNT

[Erland Sommarskog]

DataPro (da*******@yaho o.com) writes:

1.) What I'm not clear on is this....a dbcc loginfo yeilds 308 rows
marked with a 2.
Are those 'active' uncommitted transactions? What do those rows
represent?
DBCC LOGINFO is not documented, but I learn a little from SQL Server
MVP Tibor Karasiz's article on shrinking:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp. (I recommend
that you read this article in whole, although I summarise some details
below.)

Each row represents a VLF, a virtual log file, and 2 indicates that
that VLF is in use. You cannot shrink the log past the last active
VLF. And if all VLF's have a 2, then all VLFs are in use, and obviously
nothing can be shrunk.

You should backup the transaction log, either to disk or just toss it
with TRUNCATE_ONLY.
2.) If backing up a log makes the space within a log file 'reusable'
why did the log grow in size if the space in the log was made reusable?
As I discussed above, the log is apparently not reusable.
3) And finally what is the relationship between the results of a dbcc
loginfo command, a dbcc opentran and a A SELECT @@TRANCOUNT

@@trancount only reflects the state of the current process.

DBCC OPENTRAN reveals if there are any open transactions. The log
cannot be truncated past the oldest active transaction.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[DataPro]

Thanks much

[rcamarda]

This has been a really usefull thread. I have the same problem, and
debated about starting new thread or continue this one:
DBCC LOGINFO shows my last log entry is in use (as indicated by STATUS
of 2)
DBCC OPENTRAN indicates no active open transactions
My file is 89% empty, Ive backed up the log files repeatedly (first one
was large 40+ GB, now they are < 500K)
I've tried taking the DB offline and back online. The last log entry
still shows in use and I cant shrink.
I read the article, which helps identify why I cant shrink (log file in
use) but now I'm stumped.
TIA
Rob

[Erland Sommarskog]

rcamarda (ro*****@hotmai l.com) writes:

This has been a really usefull thread. I have the same problem, and
debated about starting new thread or continue this one:
DBCC LOGINFO shows my last log entry is in use (as indicated by STATUS
of 2)
DBCC OPENTRAN indicates no active open transactions
My file is 89% empty, Ive backed up the log files repeatedly (first one
was large 40+ GB, now they are < 500K)
I've tried taking the DB offline and back online. The last log entry
still shows in use and I cant shrink.
I read the article, which helps identify why I cant shrink (log file in
use) but now I'm stumped.

Is the last VLF the only that has status = 2? Or are there several?

Did you try running BACKUP TRANSACTION WITH NO_LOG? (Since you lose the
log chain, you need a full backup as well.)


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx