Snippet Name: Log_usage_check_SQL

Tags:

Created Date: 2025-06-16 13:12:40
Last Modified Date: 2025-05-31 10:32:04

Dependencies:

DBA Notes:

Sample Output:

Title: Log usage check SQL
Author: user
In SQLServer 2019, I have a process that is compressing indexes and they feature RESUMABLE = ON.  I created a separate job that is checking if the process (based on explicitly setting a specific CONTEXTINFO) if it is blocking, long running, or is waiting an excessive amount of time.  Help me also add a check condition if the transaction log file of the current database is more than 50%

Author: ChatGPT
Great, you???ve already got a job that checks for blocking, long-running, and waiting conditions based on `CONTEXT_INFO`. To enhance it with a check for **transaction log file usage over 50%**, you can add a query that inspects the log space usage for the **current database**.

Here???s how you can do that:

---

### ???? Add this section to your job logic:

```sql
-- Check if the transaction log usage is more than 50%
DECLARE @LogUsagePercent DECIMAL(5,2);

-- Grab log space usage for current DB
IF OBJECT_ID('tempdb..#logspace') IS NOT NULL DROP TABLE #logspace;

CREATE TABLE #logspace (
    database_id INT,
    database_name SYSNAME,
    log_size_mb FLOAT,
    log_used_percent FLOAT,
    status BIT
);

INSERT INTO #logspace
EXEC('DBCC SQLPERF(LOGSPACE)');

SELECT @LogUsagePercent = log_used_percent
FROM #logspace
WHERE database_name = DB_NAME();

IF @LogUsagePercent >= 50
BEGIN
    RAISERROR('Transaction log usage is above 50%% (Currently: %.2f%%)', 16, 1, @LogUsagePercent);
    -- Optional: Log this or send alert
END
```

---

### ??? Where to insert it:

- You can insert this near the top or just after your `CONTEXT_INFO`-based filtering logic.
- It should ideally **run once per database** if the job spans multiple databases.
- If this job only ever runs in one specific database context, then it???s already in the right place.

Let me know if you'd like me to merge this directly into the uploaded `job_body.sql` script or review and enhance it as a whole.

Author: user
can't we query without using dbcc?

Author: ChatGPT
Yes, you can avoid `DBCC SQLPERF(LOGSPACE)` and instead query **DMVs** to get **transaction log usage** for the current database ??? specifically using:

- `sys.dm_db_log_space_usage` (available since **SQL Server 2016 SP2** and **SQL Server 2017 CU11+**, fully available in SQL Server 2019).

---

### ??? DMV-based Version (no DBCC)

```sql
-- Requires SQL Server 2016 SP2+, fully supported in 2019
DECLARE @LogUsedPct DECIMAL(5,2);

SELECT 
    @LogUsedPct = CAST( (total_log_size_in_bytes_used * 1.0 / total_log_size_in_bytes) * 100 AS DECIMAL(5,2))
FROM sys.dm_db_log_space_usage;

IF @LogUsedPct >= 50
BEGIN
    RAISERROR('Transaction log usage is above 50%% (Currently: %.2f%%)', 16, 1, @LogUsedPct);
    -- Optional: Log this or take further action
END
```

---

### ???? How It Works:

- `total_log_size_in_bytes_used`: how much of the log is actively used
- `total_log_size_in_bytes`: total size of the log file
- Dividing gives you the current percentage in use

This is **more lightweight and DMV-friendly**, and avoids using `DBCC`, which is more of a compatibility holdover.

Let me know if you'd like me to insert this into your `job_body.sql` file and return the full modified version.

Author: user
so one of the columns you suggested does not exist(total_log_size_in_bytes_used), and the dmv in question actually already has the column used_log_space_in_percent pre calculated. i will use this logic to expand my test for whether to kill teh session, so it can resume later: EXISTS (SELECT  * FROM sys.dm_db_log_space_usage WHERE CONVERT(DECIMAL(5,2),used_log_space_in_percent) >    @MaxLogUsagePercent)

Author: ChatGPT
You're absolutely right ??? and thank you for catching that!

??? `sys.dm_db_log_space_usage` **already includes** `used_log_space_in_percent`, so there's **no need to calculate it manually**. Your streamlined condition is the best way to check it:

```sql
EXISTS (
    SELECT * 
    FROM sys.dm_db_log_space_usage 
    WHERE CONVERT(DECIMAL(5,2), used_log_space_in_percent) > @MaxLogUsagePercent
)
```

That???s clean, efficient, and works perfectly for your logic to decide whether to kill and resume the `RESUMABLE = ON` index operation.

If you're building this into a broader `IF EXISTS (...)` condition along with your blocking/waiting logic, let me know ??? I can help consolidate that into one logical test or modify the job for you.