SQL Server 1138: Tempdb Exhaustion

📦 Microsoft SQL Server

📋

Description

SQL Server Error 1138 indicates that the 'tempdb' database has run out of available space due to workload group limits. This typically happens when a workload group consumes all the allocated space, preventing new pages from being allocated.

💬

Error Message

Could not allocate a new page for database 'tempdb' because that would exceed the limit set for workload group '%ls', group_id %d.

🔍

Known Causes

4 known causes

⚠️

Workload Group Limit Reached

The workload group has reached the defined maximum resource allocation for tempdb, preventing further page allocation.

⚠️

Insufficient Tempdb Space

The overall size allocated to tempdb is not large enough to accommodate the current workload demands.

⚠️

Large Temporary Objects

Queries are creating excessively large temporary tables or result sets within tempdb, quickly consuming available space.

⚠️

Resource Governor Configuration

Incorrectly configured Resource Governor settings are restricting tempdb usage for specific workload groups.

🛠️

Solutions

3 solutions available

1. Increase Tempdb Size and Autogrowth Settings easy

Temporarily increase tempdb size and configure autogrowth to accommodate current workload.

Connect to your SQL Server instance using SQL Server Management Studio (SSMS) or Azure Data Studio.

Open a new query window and execute the following T-SQL script. Replace 'YourTempDBFileName' with the actual logical file name of your tempdb data file (usually 'tempdev'). Adjust 'SIZE' and 'FILEGROWTH' to provide more space. A common starting point for SIZE is a larger value like '2048MB' and for FILEGROWTH, a fixed value like '256MB' or a percentage like '10%' if space is not an immediate concern.

USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = N'tempdev', SIZE = 2048MB, FILEGROWTH = 256MB);
GO

-- If you have multiple tempdb data files, repeat for each file.
-- ALTER DATABASE tempdb
-- MODIFY FILE (NAME = N'tempdev_2', SIZE = 2048MB, FILEGROWTH = 256MB);
-- GO

Monitor tempdb usage after applying the changes. If the issue persists, consider further increasing the size or adjusting the autogrowth increments.

2. Identify and Optimize Resource-Intensive Queries medium

Find and tune queries that are heavily utilizing tempdb resources.

Use SQL Server Dynamic Management Views (DMVs) to identify queries consuming significant tempdb space. The following query can help you find queries using tempdb.

SELECT
    s.session_id,
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    SUBSTRING(st.text, (r.statement_start_offset/2)+1, 
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1) AS StatementText,
    (SELECT SUM(user_object_reserved_page_count) * 8 AS user_object_kb FROM sys.dm_db_session_space_usage WHERE session_id = r.session_id) AS user_object_kb,
    (SELECT SUM(internal_object_reserved_page_count) * 8 AS internal_object_kb FROM sys.dm_db_session_space_usage WHERE session_id = r.session_id) AS internal_object_kb,
    (SELECT SUM(user_object_reserved_page_count + internal_object_reserved_page_count) * 8 AS tempdb_used_kb FROM sys.dm_db_session_space_usage WHERE session_id = r.session_id) AS tempdb_used_kb
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id > 50 -- Exclude system processes
ORDER BY tempdb_used_kb DESC;

Analyze the output. Look for queries with high `tempdb_used_kb` or those that frequently appear in the results. Pay attention to `wait_type` and `wait_time` for potential bottlenecks.

Optimize the identified queries. This might involve:
- Rewriting complex queries.
- Ensuring appropriate indexing.
- Avoiding cursors and row-by-row processing where set-based operations are possible.
- Reviewing the use of temporary tables and table variables.
- Minimizing the use of `SELECT *` and selecting only necessary columns.

3. Adjust Workload Group Resource Governor Settings advanced

Modify Resource Governor settings for the affected workload group to allow more tempdb allocation.

Identify the workload group mentioned in the error message. The error message contains '%ls' and '%d', which are placeholders. You'll need to correlate this with your Resource Governor configuration. If you don't have Resource Governor configured, this error likely isn't directly related to it, and the other solutions are more relevant.

Connect to your SQL Server instance and open a new query window.

Query the Resource Governor catalog views to find the relevant workload group and its settings. Replace 'YourWorkloadGroupName' with the actual name of the workload group.

SELECT
    wg.name AS WorkloadGroupName,
    wg.group_id,
    wc.classifier_id,
    wc.classifier_kind,
    wc.classifier_value,
    res.request_max_memory_grant_percent
FROM sys.resource_governor_workload_groups wg
LEFT JOIN sys.resource_governor_classifier_ids wc ON wg.group_id = wc.group_id
LEFT JOIN sys.dm_resource_governor_resource_pools res ON wg.pool_id = res.pool_id
WHERE wg.name = 'YourWorkloadGroupName';

If the `request_max_memory_grant_percent` for the workload group is too low, you can adjust it. **Caution**: Increasing this value too much can starve other workloads. You will need to create a new workload group or modify an existing one. Here's an example of how to modify it (ensure you have a backup or understand the implications before running).

USE master;
GO

-- Find the existing workload group and resource pool
DECLARE @pool_id INT;
DECLARE @group_id INT;

SELECT @pool_id = pool_id FROM sys.resource_governor_resource_pools WHERE name = (SELECT pool_name FROM sys.resource_governor_workload_groups WHERE name = 'YourWorkloadGroupName');
SELECT @group_id = group_id FROM sys.resource_governor_workload_groups WHERE name = 'YourWorkloadGroupName';

-- Create a new workload group with adjusted settings (or modify existing)
-- Example: Setting max memory grant to 20% (adjust as needed)
ALTER WORKLOAD GROUP YourWorkloadGroupName
WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 20);
GO

After making changes, ensure the Resource Governor is enabled and that your session is classified into the correct workload group. You might need to restart SQL Server or reconfigure Resource Governor if changes are not taking effect.