1
Vote

Divide by 0 issue on the sys2.logs_usage view

description

I ran into an issue where some of the logs were not used yet whice then cause a divide by zero error. Below is my code on how to handle this issue.




WITH cte as
(
SELECT
name, 
db.log_reuse_wait_desc, 
size_mb = ls.cntr_value / 1024,
used_mb = lu.cntr_value / 1024,
CASE
    WHEN CAST(ls.cntr_value AS FLOAT) > 0
        THEN CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)
        ELSE 0
END AS used_percent,
CASE 
    WHEN CAST(ls.cntr_value AS FLOAT) > 0
        THEN CASE 
            WHEN CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) > .75
                THEN 
                    CASE 
                            /* tempdb special monitoring */ 
                            WHEN db.name = 'tempdb' AND log_reuse_wait_desc NOT IN ('CHECKPOINT', 'NOTHING') THEN 'WARNING'                     
                            WHEN db.name <> 'tempdb' THEN 'WARNING' 
                            ELSE 'OK' 
                        END 
                ELSE 'OK' 
            END
        ELSE 'Divisor = 0'
END AS log_status   
FROM
sys.databases db 
JOIN
sys.dm_os_performance_counters lu ON db.name = lu.instance_name 
JOIN
sys.dm_os_performance_counters ls ON db.name = ls.instance_name 
WHERE
lu.counter_name LIKE 'Log File(s) Used Size (KB)%' 
AND
ls.counter_name LIKE 'Log File(s) Size (KB)%' 
)
select
*
from
cte

file attachments

comments