|
|
Posted by Erland Sommarskog on 05/31/06 21:30
DataPro (datapro01@yahoo.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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|