Lesson Learned #441: Monitoring TempDB Transactions Space in Azure SQL Elastic Pools with PowerShell

Jose_Manuel_Jurado · ‎Oct 06 2023

Today, we addressed a service request from our customer who needed to identify both the session and the text of the query responsible for a significant increase in the transaction log of the tempdb among the databases within the Elastic DB Pool. This increment in the transaction log was leading to an error message: “Msg 9002, Level 17, State 4 - The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION', with the holdup identified as (XXX:NNN:YYY)"

The TempDB database in Azure SQL plays a vital role in the performance and management of your SQL instances. Monitoring its transactions closely helps ensure smooth operation and efficient troubleshooting when issues arise. In this article, we’ll explore a PowerShell script designed to monitor transactions within TempDB in the context of Azure SQL Elastic Pools.

Objective

Our main aim is to retrieve and control information about TempDB's transaction log usage by different sessions and correlate this with the executing SQL commands. By doing so, we gain invaluable insights into which sessions and queries are consuming significant amounts of the transaction log space, a critical resource. Managing the space used by transactions is crucial for preventing potential issues associated with resource contention, and for ensuring the smooth performance of TempDB and the SQL instance as a whole. This monitoring approach is significant for administrators managing systems with high transaction rates or where resources are at a premium.

PowerShell Script Overview

1. Setting Up Variables:

The script begins by initializing variables with the user’s Azure SQL server name, user ID, password, elastic pool name, and the output file's path.

$serverName = "your_server_name.database.windows.net"
$userId = "your_user_id"
$password = "your_password"
$outputFilePath = "C:\path\to\your\directory\output.json"

2. Executing Queries with Retry Policy:

Two functions, ExecuteDbNamesQuery and ExecuteTransactionQuery, are defined. Both are designed to execute SQL queries with a retry policy in case of transient failures, a common scenario in cloud environments.

3. Retrieving Database Names:

The script retrieves the names of all databases within a specified elastic pool. This step is crucial since transactions are tracked per database.

$databasesQuery = "SELECT name FROM sys.databases WHERE database_id IN (SELECT database_id FROM [sys].[database_service_objectives] WHERE elastic_pool_name = '$elasticPoolName')"

4. Transaction Monitoring Query:

For each database, a detailed query is executed. This query fetches essential transaction information, including session ID, transaction ID, database ID, database name, transaction log used (in KB), transaction begin time, transaction type, transaction state, and the executing command text.

5. Data Compilation & Export:

Results from each database are compiled and exported as a JSON file. JSON format is chosen for its readability and ease of use in subsequent data manipulation or analysis tasks.

$allResults | ConvertTo-Json | Out-File $outputFilePath

Why Monitoring Space is Essential

Preventing Overuse: Transactions that consume excessive log space can lead to performance degradation and can cause the system to run out of space.
Optimizing Performance: Understand which sessions and queries are heavy users of the transaction log, and optimize or redesign them for efficient log usage.
Resource Management: Managing the use of transaction log space is crucial for cost control and efficient resource allocation in environments where resources are finite.
Troubleshooting: Having detailed information on transaction log usage aids in quickly identifying and resolving transaction processing issues.

Understanding the Results

Session ID & Transaction ID: Identifies sessions and transactions within those sessions.
Database ID & Name: These fields provide context for understanding the transaction's role and impact.
Log Used (in KB) & Begin Time: Helps identify long-running or resource-intensive transactions.
Transaction Type & State: Describes the nature and current state of the transaction.
Executing Command Text: Retrieves the SQL command being executed in the session, offering insight into the operations being performed.

Conclusion

Monitoring and controlling TempDB transactions, especially regarding transaction log space, is indispensable in Azure SQL Elastic Pools. The provided PowerShell script offers a robust solution for retrieving, understanding, and analyzing transaction data, aiding database administrators in their ongoing management and optimization efforts. Always ensure to run the script in a secure and test environment before deploying it in a production setting.

$serverName = "your_server_name.database.windows.net"
$userId = "your_user_id"
$password = "your_password"
$outputFilePath = "C:\path\to\your\directory\output.json"
$elasticPoolName = "elasticdbpoolname"

function ExecuteDbNamesQuery($connectionString, $query) {
    $maxRetries = 3
    $retryInterval = 2 # seconds
    $results = @()
    for ($i = 0; $i -lt $maxRetries; $i++) {
        try {
            $conn = New-Object System.Data.SqlClient.SqlConnection
            $conn.ConnectionString = $connectionString
            $conn.Open()
            $command = $conn.CreateCommand()
            $command.CommandText = $query
            $reader = $command.ExecuteReader()

            while ($reader.Read()) {
                $results += "$($reader["name"])"
            }

            $conn.Close()
            return $results
        } catch {
            Write-Output "Error: $_"
            Start-Sleep -Seconds $retryInterval
        }
    }
    Write-Output "The query failed after $maxRetries."
    return $results
}

function ExecuteTransactionQuery($connectionString, $query) {
    $maxRetries = 3
    $retryInterval = 2 # seconds
    $results = @()
    for ($i = 0; $i -lt $maxRetries; $i++) {
        try {
            $conn = New-Object System.Data.SqlClient.SqlConnection
            $conn.ConnectionString = $connectionString
            $conn.Open()
            $command = $conn.CreateCommand()
            $command.CommandText = $query
            $reader = $command.ExecuteReader()

            while ($reader.Read()) {
                $row = @{
                    SessionId = $reader["session_id"]
                    TransactionId = $reader["transaction_id"]
                    DatabaseId = $reader["database_id"]
                    DatabaseName = $reader["database_name"]
                    LogUsedKb = $reader["database_transaction_log_used_Kb"]
                    BeginTime = $reader["database_transaction_begin_time"]
                    TypeDesc = $reader["transaction_type_desc"]
                    StateDesc = $reader["transaction_state_desc"]
                    CommandText = $reader["executing_command_text"]
                }
                $results += ,$row
            }

            $conn.Close()
            return $results
        } catch {
            Write-Output "Error: $_"
            Start-Sleep -Seconds $retryInterval
        }
    }
    Write-Output "The query failed after $maxRetries ."
    return $results
}

$masterConnString = "Server=$serverName;Database=master;User Id=$userId;Password=$password;"
$databasesQuery = "SELECT name FROM sys.databases WHERE database_id IN (SELECT database_id FROM [sys].[database_service_objectives] WHERE elastic_pool_name = '$elasticPoolName')"
$databases = ExecuteDbNamesQuery $masterConnString $databasesQuery

$allResults = @()
foreach ($db in $databases) {
    $dbConnString = "Server=$serverName;Database=$db;User Id=$userId;Password=$password;"
    $transactionQuery = @"
SELECT 
    ST.session_id,
    ST.transaction_id,
    DT.database_id,
    CASE WHEN D.name IS NULL AND DT.database_id = 2 THEN 'TEMPDB' ELSE D.name END as [database_name],
    CONVERT(numeric(18,2), DT.database_transaction_log_bytes_used / 1024.0 ) as [database_transaction_log_used_Kb],
    DT.database_transaction_begin_time,
    CASE database_transaction_type
        WHEN 1 THEN 'Read/write transaction'
        WHEN 2 THEN 'Read-only transaction'
        WHEN 3 THEN 'System transaction'
        WHEN 4 THEN 'Distributed transaction'
    END as [transaction_type_desc],
    CASE database_transaction_state
        WHEN 0 THEN 'The transaction has not been completely initialized yet'
        WHEN 1 THEN 'The transaction has been initialized but has not started'
        WHEN 2 THEN 'The transaction is active'
        WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions'
        WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place'
        WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
        WHEN 6 THEN 'The transaction has been committed'
        WHEN 7 THEN 'The transaction is being rolled back'
        WHEN 8 THEN 'The transaction has been rolled back'
    END as [transaction_state_desc],
    substring
(REPLACE
(REPLACE
(SUBSTRING
(T.text
, (req.statement_start_offset/2) + 1
, (
(CASE statement_end_offset
WHEN -1
THEN DATALENGTH(T.text)
ELSE req.statement_end_offset
END
- req.statement_start_offset)/2) + 1)
, CHAR(10), ' '), CHAR(13), ' '), 1, 4000) as [executing_command_text]
FROM 
    sys.dm_tran_database_transactions DT
    INNER JOIN sys.dm_tran_session_transactions ST
        ON DT.transaction_id = ST.transaction_id
    LEFT JOIN sys.databases D
        ON DT.database_id = D.database_id
	INNER JOIN sys.dm_exec_requests req
	    ON ST.session_id = Req.session_id
    OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) as T
ORDER BY 
    ST.session_id
"@
    $results = ExecuteTransactionQuery $dbConnString $transactionQuery
    $allResults += $results
}

# Convierte todos los resultados a JSON y escribe en el archivo
$allResults | ConvertTo-Json | Out-File $outputFilePath

Additional Information: Azure SQL DB and TEMPDB usage tracking - Microsoft Community Hub

This script contains an example how to fill up the transaction log of TEMPDB:

-- Create a temporal table
CREATE TABLE #LargeTempTable (
    ID INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(50),
    Value INT
);

-- Variables
DECLARE @counter INT = 0;
DECLARE @name NVARCHAR(50);
DECLARE @value INT;

-- Start the transaction
BEGIN TRANSACTION;

-- Insert loop
WHILE @counter < 1000000 -- 
BEGIN
    SET @name = 'Name' + CAST(@counter AS NVARCHAR(50));
    SET @value = CAST(RAND() * 1000 AS INT);

    INSERT INTO #LargeTempTable (Name, Value)
    VALUES (@name, @value);

    SET @counter = @counter + 1;
END

-- ROLLBACK TRANSACTION; 
DROP TABLE #LargeTempTable;

Using MARS with multiple executions

We encountered an issue where our client reported being unable to view the text of a query or receiving null query text.

After conducting a thorough investigation, we discovered that the customer was executing multiple commands within a single session (e.g., SELECT, INSERT, BULK INSERT). Additionally, with Multiple Active Result Sets (MARS) enabled, so, sometimes we could not have an active execution at TCP level but we could have a session level. To address this issue, we need to make a modification to the query that retrieves information by utilizing the "most_recent_sql_handle" field from the connections DMV.

SELECT
    ST.session_id,
    ST.transaction_id,
    DT.database_id,
    conn.net_transport,
    CASE WHEN D.name IS NULL AND DT.database_id = 2 THEN 'TEMPDB' ELSE D.name END as [database_name],
    CONVERT(numeric(18,2), DT.database_transaction_log_bytes_used / 1024.0 ) as [database_transaction_log_used_Kb],
    DT.database_transaction_begin_time,
    CASE database_transaction_type
        WHEN 1 THEN 'Read/write transaction'
        WHEN 2 THEN 'Read-only transaction'
        WHEN 3 THEN 'System transaction'
        WHEN 4 THEN 'Distributed transaction'
    END as [transaction_type_desc],
    CASE database_transaction_state
        WHEN 0 THEN 'The transaction has not been completely initialized yet'
        WHEN 1 THEN 'The transaction has been initialized but has not started'
        WHEN 2 THEN 'The transaction is active'
        WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions'
        WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place'
        WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
        WHEN 6 THEN 'The transaction has been committed'
        WHEN 7 THEN 'The transaction is being rolled back'
        WHEN 8 THEN 'The transaction has been rolled back'
    END as [transaction_state_desc],
    T.text as [executing_command_text]
FROM
    sys.dm_tran_database_transactions DT
    INNER JOIN sys.dm_tran_session_transactions ST
        ON DT.transaction_id = ST.transaction_id
    LEFT JOIN sys.databases D
        ON DT.database_id = D.database_id
    INNER JOIN sys.dm_exec_connections conn
        ON ST.session_id = conn.session_id
    OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) as T
WHERE DT.database_id <> 32767
ORDER BY
    ST.session_id

This modified query leverages the "most_recent_sql_handle" field from the connections DMV to ensure that even in scenarios with multiple commands in a single session and MARS enabled, the query text will be accessible for monitoring and troubleshooting purposes.

Products (50)

Special Topics (27)

Video Hub (462)

Most Active Hubs