Problem
Error Message:
Subject: SQL Server Alert System: 'ServerName - Sev:017 -
INNSUFIFIENT RESOURCES' occurred on \\ServerName
DESCRIPTION: Could
not allocate space for object 'dbo.SORT temporary run storage: 150684339732480' in database 'tempdb' because
the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files,
dropping objects in the filegroup, adding additional files to the filegroup, or
setting autogrowth on for existing files in the filegroup.
Troubleshooting
1. Check tempdb properties
The version of SQL Server on this server is 2014 RTM CU14. Since the application is not certified for SQL Server 2014 SP1 and above, so we are unable to install more patches. Autogrowth of tempdb is disabled. The error message tells me there is no space in tempdb any more.
The first step is to check the properties of tempdb from SSMS:
It shows that a lot of space available, strange. If this is true, then we should not have gotten the error message.
2. Check DM view sys.dm_db_files_space_usage
DM view sys.dm_db_files_space_usage returns space usage information for each file in the database. Microsoft online book provides more details here. Run this query will get the space usage information from tempdb:
SELECT *
FROM [tempdb].[sys].[dm_db_file_space_usage]
The results show that the tempdb has been filled up by internal objects. According to Microsoft online book, the following objects are included in the internal object page counters:
- Work tables for cursor or spool operations and temporary large object (LOB) storage
- Work files for operations such as a hash join
- Sort runs
The internal objects should be deallocated after being used. However, in this case, they are not deallocated, and keep growing until reach the space limit.
3. Reboot server during planned application outage
A reboot will recreate the tempdb, therefore cleanup the internal objects.
Future Work
The next step will be monitoring the growth of internal objects, and to setup alert for tempdb space usage.
