PS vs sys.databases

Question

Hello,

I use daily db reports that are created in PowerShell and noted that the output of LogReuseWaitStatus is not always the same as that of log_reuse_wait_desc:

1) $dbname = Get-SQLDatabase
ForEach ($db in $dbname)
{
Write-Output "$($db.Name), $($db.Status), ..., $($db.LogReuseWaitStatus)" >> C:\REPORTS\SQL\SQL-DB.txt
}

2) SELECT D.name,
D.log_reuse_wait_desc
FROM sys.databases AS D

As you see the Nothing and LogBackup are the same for both methods while Other in PS may mean Availability Replica in TSQL and 13 may mean OLDEST_Page.

I expected that both PS and TSQL would return the same output... - is that distinction by design?

Regards,
Michael

Answer 1

Checking the documentation for sys.databases, I see that 13 is indeed OLDEST_PAGE, so in this case PS has the same thing - it just doesn't have the translation correct.

I also see that for log_resuse_wait there are two meanings for the value 9:
9 = An Always On Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database.
9 = Other (Transient)

So again, this seems to be due to that the PS module is doing it's own translation rather than relying on log_reuse_wait_desc.

Answer 2

Thank you very much, ErlandSommarskog!

Regards,
Michael