Open transactions

Is there a way to tell how much of a transaction log file has open
transactions?
For example: You have a 10 MB transaction log that is 50% full ( DBCC
sqlperf(logspace) ) and you backup the log. After the log backup you see it
is 25% full. Therefore, 25% of the transaction log held open transactions.
How can I tell there this before I back the log up?I am not sure how you can tell without looking at each entry in the log and
calculating this all. But I don't agree with your assumption based on your
example. Just because it freed up 25% space does not mean that there was
25% open transactions. A single open transaction in the wrong place can
prevent the log from being truncated and the space reused. That has no
correlation to how many open transactions there were.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospan@.nospam.org> wrote in message
news:O4juyqKHIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Is there a way to tell how much of a transaction log file has open
> transactions?
>
> For example: You have a 10 MB transaction log that is 50% full ( DBCC
> sqlperf(logspace) ) and you backup the log. After the log backup you see
> it is 25% full. Therefore, 25% of the transaction log held open
> transactions.
> How can I tell there this before I back the log up?
>|||On Thu, 1 Nov 2007 10:14:43 -0700, "Jay" <nospan@.nospam.org> wrote:
>Is there a way to tell how much of a transaction log file has open
>transactions?
>
>For example: You have a 10 MB transaction log that is 50% full ( DBCC
>sqlperf(logspace) ) and you backup the log. After the log backup you see it
>is 25% full. Therefore, 25% of the transaction log held open transactions.
>How can I tell there this before I back the log up?
Besides the points that Andrew covered, there is also the matter of
Virtual Log Files. You can read up on them in the Books on Line (BOL)
but essentially it is a unit of space within the log file. Information
on space in the log is, if I have things right, in multiples of the
VLF, so 25% full could simply mean there are 4 VLFs and only one is in
use - but it might be far from full.
Roy Harvey
Beacon Falls, CT|||> A single open transaction in the wrong place can prevent the log from
> being truncated and the space reused.
OK, this is confusing. I'm not talking about shrinking the log file (where I
would understand this), I'm talking about marking transactions as backed up
so the the space in the .ldf can be reused.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ebpe%230LHIHA.5544@.TK2MSFTNGP02.phx.gbl...
>I am not sure how you can tell without looking at each entry in the log and
>calculating this all. But I don't agree with your assumption based on your
>example. Just because it freed up 25% space does not mean that there was
>25% open transactions. A single open transaction in the wrong place can
>prevent the log from being truncated and the space reused. That has no
>correlation to how many open transactions there were.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:O4juyqKHIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Is there a way to tell how much of a transaction log file has open
>> transactions?
>>
>> For example: You have a 10 MB transaction log that is 50% full ( DBCC
>> sqlperf(logspace) ) and you backup the log. After the log backup you see
>> it is 25% full. Therefore, 25% of the transaction log held open
>> transactions.
>> How can I tell there this before I back the log up?
>|||Are you saying that DBCC sqlperf(logspace) reports a number that corasponds
to the VLF's and not to the actual space used? If so, that would explain a
couple other things, like how the really small .ldf files constantly have
such a large % used, when nothing is going on in that DB.
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
news:ehcki31hv9q36sd8n9q7vs52c7ktflikri@.4ax.com...
> On Thu, 1 Nov 2007 10:14:43 -0700, "Jay" <nospan@.nospam.org> wrote:
>>Is there a way to tell how much of a transaction log file has open
>>transactions?
>>
>>For example: You have a 10 MB transaction log that is 50% full ( DBCC
>>sqlperf(logspace) ) and you backup the log. After the log backup you see
>>it
>>is 25% full. Therefore, 25% of the transaction log held open transactions.
>>How can I tell there this before I back the log up?
> Besides the points that Andrew covered, there is also the matter of
> Virtual Log Files. You can read up on them in the Books on Line (BOL)
> but essentially it is a unit of space within the log file. Information
> on space in the log is, if I have things right, in multiples of the
> VLF, so 25% full could simply mean there are 4 VLFs and only one is in
> use - but it might be far from full.
> Roy Harvey
> Beacon Falls, CT|||On Thu, 1 Nov 2007 14:21:00 -0700, "Jay" <nospan@.nospam.org> wrote:
>Are you saying that DBCC sqlperf(logspace) reports a number that corasponds
>to the VLF's and not to the actual space used? If so, that would explain a
>couple other things, like how the really small .ldf files constantly have
>such a large % used, when nothing is going on in that DB.
That is my understanding, or misunderstanding as the case may be.
Roy Harvey
Beacon Falls, CT|||With a DB that is NOT in SIMPLE mode a VLF can only be truncated and reused
when there are no Open Trans and it has been check pointed and backed up.
If there is a single open tran in a VLF with 1000 committed trans the VLF
can not be reused. If you have 4 VLF's and 2 VLF's are in use the results of
DBCC SQLPERF(Logspace) will show it as being 50% used or empty which ever
way you want to look at it. But that does not mean you had 50% uncommitted
transactions. You only need 2, one in each VLF to make it appear that way.
So in your example:
>> For example: You have a 10 MB transaction log that is 50% full ( DBCC
>> sqlperf(logspace) ) and you backup the log. After the log backup you see
>> it is 25% full. Therefore, 25% of the transaction log held open
>> transactions.
you imply that 25% of the log was uncommitted transactions. It may have only
been a fraction of a % of actual uncommitted transactions. If all you care
about is usable space then fine state it as how much space is usable at that
point in time or not. Your question of:
>> Is there a way to tell how much of a transaction log file has open
>> transactions?
is not that simple based on usable space. You would have to use something
like fn_dblog() (google for more details) to identify each LSN to see what
state it was in. This is just not practical. I went thru this exercise not
to be picky about wording but to ensure you understand how VLF's affect the
log in hits way.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospan@.nospam.org> wrote in message
news:%238PZLyMHIHA.2328@.TK2MSFTNGP03.phx.gbl...
>> A single open transaction in the wrong place can prevent the log from
>> being truncated and the space reused.
> OK, this is confusing. I'm not talking about shrinking the log file (where
> I would understand this), I'm talking about marking transactions as backed
> up so the the space in the .ldf can be reused.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ebpe%230LHIHA.5544@.TK2MSFTNGP02.phx.gbl...
>>I am not sure how you can tell without looking at each entry in the log
>>and calculating this all. But I don't agree with your assumption based on
>>your example. Just because it freed up 25% space does not mean that there
>>was 25% open transactions. A single open transaction in the wrong place
>>can prevent the log from being truncated and the space reused. That has no
>>correlation to how many open transactions there were.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:O4juyqKHIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Is there a way to tell how much of a transaction log file has open
>> transactions?
>>
>> For example: You have a 10 MB transaction log that is 50% full ( DBCC
>> sqlperf(logspace) ) and you backup the log. After the log backup you see
>> it is 25% full. Therefore, 25% of the transaction log held open
>> transactions.
>> How can I tell there this before I back the log up?
>>
>sql