80k active rows when I run DBCC LOGINFO(XXX). Why?

Hello all,
I am having some trouble shrinking my logs which are currently approx. 21GB.
I did some research and found some pages that say the rows should have a
status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
approx 80k rows which all have a status of 2. What does this mean?
I already made a full backup of the database, and a separate backup of the
logs but the results remain the same.
I would appreciate if someone can explain what this means, what can I do
about, and why do I have 80k active rows. Where can I find an explanation of
this so I can learn about it? Thanks!
JohnnyYou probably created the file small and had bunch of autogrowths. Here's a reply to similar Q I
posted earlier today:
Log is emptied (2 to 0) then you backup log. Backup log, check if 2 at the end. 2 at the end sets
limit of shrink. What you do is alternate backup log and shrink a few times. Then you alter database
and pre-allocate a much as you probably need.
In addition, read about the virtual logfile concept in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:CC046CC3-867B-4CB7-AEF7-B81B3F622151@.microsoft.com...
> Hello all,
> I am having some trouble shrinking my logs which are currently approx. 21GB.
> I did some research and found some pages that say the rows should have a
> status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
> approx 80k rows which all have a status of 2. What does this mean?
> I already made a full backup of the database, and a separate backup of the
> logs but the results remain the same.
> I would appreciate if someone can explain what this means, what can I do
> about, and why do I have 80k active rows. Where can I find an explanation of
> this so I can learn about it? Thanks!
> Johnny|||Johnny,
> I am having some trouble shrinking my logs which are currently approx. 21GB.
> I did some research and found some pages that say the rows should have a
> status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
> approx 80k rows which all have a status of 2. What does this mean?
The active part of the transaction log (uncommitted transactions). This can
be caused by long running transactions and can be a problem even if the
databse is using simple recovery model. See "Checkpoints and the Active
Portion of the Log" in BOL.
Try executing "dbcc opentran" to check all active transactions.
AMB
"Johnny" wrote:
> Hello all,
> I am having some trouble shrinking my logs which are currently approx. 21GB.
> I did some research and found some pages that say the rows should have a
> status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
> approx 80k rows which all have a status of 2. What does this mean?
> I already made a full backup of the database, and a separate backup of the
> logs but the results remain the same.
> I would appreciate if someone can explain what this means, what can I do
> about, and why do I have 80k active rows. Where can I find an explanation of
> this so I can learn about it? Thanks!
> Johnny|||Sorry, forgot to mention that the active part of the transaction log should
be at the begining, if not sql server can not shrink the transaction log.
AMB
"Alejandro Mesa" wrote:
> Johnny,
> > I am having some trouble shrinking my logs which are currently approx. 21GB.
> > I did some research and found some pages that say the rows should have a
> > status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
> > approx 80k rows which all have a status of 2. What does this mean?
> The active part of the transaction log (uncommitted transactions). This can
> be caused by long running transactions and can be a problem even if the
> databse is using simple recovery model. See "Checkpoints and the Active
> Portion of the Log" in BOL.
> Try executing "dbcc opentran" to check all active transactions.
>
> AMB
> "Johnny" wrote:
> > Hello all,
> >
> > I am having some trouble shrinking my logs which are currently approx. 21GB.
> > I did some research and found some pages that say the rows should have a
> > status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
> > approx 80k rows which all have a status of 2. What does this mean?
> >
> > I already made a full backup of the database, and a separate backup of the
> > logs but the results remain the same.
> >
> > I would appreciate if someone can explain what this means, what can I do
> > about, and why do I have 80k active rows. Where can I find an explanation of
> > this so I can learn about it? Thanks!
> >
> > Johnny