Hello,

Here is a view that gives you information about the number of unscanned
pages in a log for a rep agent or ltm. It works for logs that have
multiple allocations as well. Try it and let me know if you find any bugs.
Thanks, Ryan Putnam

CREATE VIEW SINF_REP_LOG_STATUS
AS
-- Author: Ryan Putnam
-- Intent: Catalog View

SELECT DB_NAME() AS "DBNAME",
SUM(su.size) AS "TOTAL_SIZE_PAGES",
MAX(RESERVED_PGS(si.id, si.doampg)) AS "USED_LOG_PAGES",
SUM(su.size) - MAX(RESERVED_PGS(si.id, si.doampg)) AS
"FREE_LOG_PAGES",
SUM(CASE
WHEN si.first BETWEEN su.lstart AND su.lstart + su.size
THEN RESERVED_PGS(si.id, si.doampg) + si.first - su.lstart
ELSE su.size
END) AS "VIRTUAL_LAST_LOG_PAGE",
SUM(CASE
WHEN sl.page BETWEEN su.lstart AND su.lstart + su.size
THEN sl.page - su.lstart
ELSE su.size
END) AS "VIRTUAL_TRUNC_PAGE",
CASE
WHEN SUM(CASE
WHEN si.first BETWEEN su.lstart AND su.lstart +
su.size
THEN RESERVED_PGS(si.id, si.doampg) + si.first -
su.lstart
ELSE su.size
END)
-
SUM(CASE
WHEN sl.page BETWEEN su.lstart AND su.lstart + su.size
THEN sl.page - su.lstart
ELSE su.size
END) >= 0
THEN SUM(CASE
WHEN si.first BETWEEN su.lstart AND su.lstart +
su.size
THEN RESERVED_PGS(si.id, si.doampg) + si.first -
su.lstart
ELSE su.size
END)
-
SUM(CASE
WHEN sl.page BETWEEN su.lstart AND su.lstart +
su.size
THEN sl.page - su.lstart
ELSE su.size
END)
ELSE 0
END AS "UNSCANNED_PAGES",
MAX(si.first) AS "FIRST_LOG_PAGE",
MAX(sl.page) AS "TRUNC_LOG_PAGE"
FROM master.dbo.sysusages su,
master.dbo.syslogshold sl,
sysindexes si
WHERE su.dbid = DB_ID()
AND su.segmap & 4 = 4
AND si.id = 8
AND su.dbid = sl.dbid
AND sl.name LIKE "%replication_truncation_point"
GROUP
BY su.dbid
go