August 30, 2021 at 1:53 am
Hello experts,
There is something going on with the tempdb on a host I administer. There is enough disk space, so it looks like the tempdb data files hit the specified auto-grow limit.
I just want to know if there is any way to reclaim space in tempdb in this case without having to change the auto-growth settings and restart SQL Server.
Thanks for any help!
-- webrunner
I'm seeing these errors:
Error: 1105, Severity: 17, State: 2.
Could not allocate space for object 'dbo.SORT temporary run storage: ...' 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.
Error: 1101, Severity: 17, State: 1.
Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
I found these queries at this page:
-- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;
-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;
-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;
-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS ,
(SUM(user_object_reserved_page_count)*1.0/128) AS
FROM tempdb.sys.dm_db_file_space_usage;
Results:
free pages free space in MB
-------------------- ---------------------------------------
32472 253.687500
(1 row affected)
version store pages used version store space in MB
------------------------ ---------------------------------------
24 0.187500
(1 row affected)
internal object pages used internal object space in MB
-------------------------- ---------------------------------------
7823152 61118.375000
(1 row affected)
user object pages used user object space in MB
---------------------- ---------------------------------------
424 3.312500
(1 row affected)
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 30, 2021 at 9:16 am
Looks like tempdb is mostly used for huge cache data sets of hash joins, Cartesian product joins, and sorting in tempdb.
Sorting usually happens in tempdb when there is no memory available for that. Either all the memory is eaten by extremely expensive other queries or the data set intended to be sorted is too big to fit even in tempdb.
Check the execution plan for the failing query - you might find those trillions or quadrillions of rows to be sorted in there.
Rectify the query, make it not so expensive in terms of resources - the issue will be gone.
_____________
Code for TallyGenerator
August 30, 2021 at 2:20 pm
Thank you, Sergiy, I will look into finding the expensive query that is eating up space in tempdb.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply