Checkpoints

Do I understand correctly that iIf I execute the CHECKPOINT statement from
query analyzer for a selected database, all uncommitted transactions in the
transaction log are physically written to the database at that time?
If so then do I need to manually truncate the log at another time to reduce
it's size. Because I'm assuming the CHECKPOINT does not automatically do
that.
Thanks for the clarification.No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
The "write-ahead log" (WAL) protocol used by SQL Server does not require
that all pages changed by a transaction are flushed to disk at the time of
the transaction commit; it only requires that the log records that affect
those transactions be persisted in the transaction log so that those
operations can be undone or redone in the case of a crash. The dirty pages
themselves can be written at the database system's lesiure. The number of
dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
interval) directly affects recovery time.
Books Online topic "CHECKPOINT" describes its function fairly well.
Thanks,
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
> Do I understand correctly that iIf I execute the CHECKPOINT statement from
> query analyzer for a selected database, all uncommitted transactions in
> the
> transaction log are physically written to the database at that time?
> If so then do I need to manually truncate the log at another time to
> reduce
> it's size. Because I'm assuming the CHECKPOINT does not automatically do
> that.
> Thanks for the clarification.|||Ryan,
Thanks very much... you're explanation makes it very clear!
"Ryan Stonecipher [MSFT]" wrote:

> No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to dis
k.
> The "write-ahead log" (WAL) protocol used by SQL Server does not require
> that all pages changed by a transaction are flushed to disk at the time of
> the transaction commit; it only requires that the log records that affect
> those transactions be persisted in the transaction log so that those
> operations can be undone or redone in the case of a crash. The dirty page
s
> themselves can be written at the database system's lesiure. The number of
> dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
> interval) directly affects recovery time.
> Books Online topic "CHECKPOINT" describes its function fairly well.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
> news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
>
>

Checkpoints

Do I understand correctly that iIf I execute the CHECKPOINT statement from
query analyzer for a selected database, all uncommitted transactions in the
transaction log are physically written to the database at that time?
If so then do I need to manually truncate the log at another time to reduce
it's size. Because I'm assuming the CHECKPOINT does not automatically do
that.
Thanks for the clarification.No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
The "write-ahead log" (WAL) protocol used by SQL Server does not require
that all pages changed by a transaction are flushed to disk at the time of
the transaction commit; it only requires that the log records that affect
those transactions be persisted in the transaction log so that those
operations can be undone or redone in the case of a crash. The dirty pages
themselves can be written at the database system's lesiure. The number of
dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
interval) directly affects recovery time.
Books Online topic "CHECKPOINT" describes its function fairly well.
Thanks,
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
> Do I understand correctly that iIf I execute the CHECKPOINT statement from
> query analyzer for a selected database, all uncommitted transactions in
> the
> transaction log are physically written to the database at that time?
> If so then do I need to manually truncate the log at another time to
> reduce
> it's size. Because I'm assuming the CHECKPOINT does not automatically do
> that.
> Thanks for the clarification.|||Ryan,
Thanks very much... you're explanation makes it very clear!
"Ryan Stonecipher [MSFT]" wrote:
> No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
> The "write-ahead log" (WAL) protocol used by SQL Server does not require
> that all pages changed by a transaction are flushed to disk at the time of
> the transaction commit; it only requires that the log records that affect
> those transactions be persisted in the transaction log so that those
> operations can be undone or redone in the case of a crash. The dirty pages
> themselves can be written at the database system's lesiure. The number of
> dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
> interval) directly affects recovery time.
> Books Online topic "CHECKPOINT" describes its function fairly well.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
> news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
> > Do I understand correctly that iIf I execute the CHECKPOINT statement from
> > query analyzer for a selected database, all uncommitted transactions in
> > the
> > transaction log are physically written to the database at that time?
> >
> > If so then do I need to manually truncate the log at another time to
> > reduce
> > it's size. Because I'm assuming the CHECKPOINT does not automatically do
> > that.
> >
> > Thanks for the clarification.
>
>

Checkpoints

Do I understand correctly that iIf I execute the CHECKPOINT statement from
query analyzer for a selected database, all uncommitted transactions in the
transaction log are physically written to the database at that time?
If so then do I need to manually truncate the log at another time to reduce
it's size. Because I'm assuming the CHECKPOINT does not automatically do
that.
Thanks for the clarification.
No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
The "write-ahead log" (WAL) protocol used by SQL Server does not require
that all pages changed by a transaction are flushed to disk at the time of
the transaction commit; it only requires that the log records that affect
those transactions be persisted in the transaction log so that those
operations can be undone or redone in the case of a crash. The dirty pages
themselves can be written at the database system's lesiure. The number of
dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
interval) directly affects recovery time.
Books Online topic "CHECKPOINT" describes its function fairly well.
Thanks,
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
> Do I understand correctly that iIf I execute the CHECKPOINT statement from
> query analyzer for a selected database, all uncommitted transactions in
> the
> transaction log are physically written to the database at that time?
> If so then do I need to manually truncate the log at another time to
> reduce
> it's size. Because I'm assuming the CHECKPOINT does not automatically do
> that.
> Thanks for the clarification.
|||Ryan,
Thanks very much... you're explanation makes it very clear!
"Ryan Stonecipher [MSFT]" wrote:

> No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
> The "write-ahead log" (WAL) protocol used by SQL Server does not require
> that all pages changed by a transaction are flushed to disk at the time of
> the transaction commit; it only requires that the log records that affect
> those transactions be persisted in the transaction log so that those
> operations can be undone or redone in the case of a crash. The dirty pages
> themselves can be written at the database system's lesiure. The number of
> dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
> interval) directly affects recovery time.
> Books Online topic "CHECKPOINT" describes its function fairly well.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
> news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
>
>

Checking for Null Values in a Table/Matrix Cell

What would be an equivalent expression for ISNULL(datafield, 0) for a table/matrix cell? I am using iif( Len().. to find out if there is something in the cell, and displaying zero in the cell if the length of cell item is 0, however am wondering if there is any better/elegant way of doing that?

=iif(fieldname = nothing, 0, fieldname)|||um, wouldn't that be =iif(fieldname = dbnull.value,0,fieldname) ?|||Actually, SSRS converts dbnull to an actual null in the report, so using:

iif(fields!fieldname.value = nothing , 0, fields!fieldname.value)

or

iif(IsNothing(fields!fieldname.value), 0, fields!fieldname.value)

seems to be the MSFT suggested way to trap for nulls.

iif(fields!fieldname.value = dbnull.value, 0, fields!fieldname.value) won't work. You might be able to get away with iif(fields!fieldname.value is system.dbnull.value, 0, fields!fieldname.value), but people have reported some issues with using System.DBNull and all the MSFT guys say to use "nothing".