--- description: If you are using a script to track temp DB size, append the script from this article to identify the root cause for the temp DB filling. title: How to Find the Reason Why TempDB is Full in Your SQL Server image: https://www.datanumen.com/blogs/wp-content/uploads/2017/06/tempdb.jpg --- - - - [Home](https://www.datanumen.com/blogs/) > [Solutions](https://www.datanumen.com/blogs/category/solutions/) > [Database Solutions](https://www.datanumen.com/blogs/category/solutions/database-solutions/) > [SQL Server Solutions](https://www.datanumen.com/blogs/category/solutions/database-solutions/sql-server-solutions/) > How to Find the Reason Why TempDB is Full in Your SQL Server # How to Find the Reason Why TempDB is Full in Your SQL Server [SQL Server Solutions](https://www.datanumen.com/blogs/category/solutions/database-solutions/sql-server-solutions/) September 29, 2025 Share Now: - - - - - - - - *If the temp DB database of your SQL Server runs out of space, it can cause major disruptions in your production environment and can interrupt user applications from successful completion. If you are using a script to track temp DB size, append the script from this article to identify the root cause for the temp DB filling.* ## Tempdb full – a common scenario ![Tempdb ](https://www.datanumen.com/blogs/wp-content/uploads/2017/06/tempdb.jpg "Tempdb")Poorly written queries might create several temporary objects resulting in a growing tempdb database. This will end in disk space alerts and might cause server problems. When many SQL Server database administrators’ find it very difficult to shrink the tempdb, they immediately opt for server restart. If had tried all methods to shrink the tempdb database and if it is still not shrinking, the last option is to restart SQL Service through configuration manager. Thus, your disk space alerts would stop and server problems also would stop. However, restarting tempdb might not be available to you if the issue had occurred on a production server. ## DBCC ![DBCC](https://www.datanumen.com/blogs/wp-content/uploads/2017/06/dbcc-300x171.jpg "DBCC")In such cases, there are several DBCC commands which when run would allow you to shrink tempdb. If you had set up scripts to proactively monitor the tempdb size, you can use this script to find out fillers of tempdb. As of now, this script would get executed on all linkedservers. You can control it easily by putting a where clause. You can make the script to be executed only when there is a problem with your tempdb. The table @Tserver is used to store all your linked server names. For a tempdb database, [SQL corruption](https://www.datanumen.com/sql-recovery/) would change the status of the database as SUSPECT and this will interrupt SQL Server service from starting. ``` DECLARE @Tserver TABLE   (      cserver VARCHAR(200)   ) INSERT INTO @Tserver VALUES      ('SERVERNAME') DECLARE @LogTable TABLE   (      cservername VARCHAR(200),      cssionid    SMALLINT,      callocmb    BIGINT,      cdeallocmb  BIGINT,      ctext       VARCHAR(4000),      cstatement  VARCHAR(4000)   ) DECLARE c1 CURSOR FOR   SELECT *   FROM   @Tserver DECLARE @cmd    NVARCHAR(4000),         @server VARCHAR(200) OPEN c1 FETCH next FROM c1 INTO @server WHILE @@FETCH_STATUS = 0   BEGIN       SET @cmd = 'EXEC(''use tempdb Declare @Table1 table  ( cdeallopages bigint, callopages bigint, cssionid smallint, creqstid int ) insert into @Table1 SELECT SUM(internal_objects_dealloc_page_count), SUM(internal_objects_alloc_page_count), session_id, request_id  FROM sys.dm_db_task_space_usage WITH (NOLOCK) WHERE session_id <> @@SPID GROUP BY session_id, request_id declare @Table2 table ( cssionid smallint, callocmb bigint, cdeallocmb bigint, ctext varchar(4000), cstatement varchar(4000) ) insert into @Table2 SELECT TBL1.cssionid, TBL1.callopages * 1.0 / 128 , TBL1.cdeallopages * 1.0 / 128 , TBL3.text, ISNULL( NULLIF( SUBSTRING( TBL3.text,  TBL2.statement_start_offset / 2,  CASE WHEN TBL2.statement_end_offset < TBL2.statement_start_offset  THEN 0  ELSE( TBL2.statement_end_offset - TBL2.statement_start_offset ) / 2 END ), '''''''' ), TBL3.text ) FROM @Table1 AS TBL1 INNER JOIN sys.dm_exec_requests TBL2 WITH (NOLOCK) ON  TBL1.cssionid = TBL2.session_id AND TBL1.creqstid = TBL2.request_id OUTER APPLY sys.dm_exec_sql_text(TBL2.sql_handle) AS TBL3 OUTER APPLY sys.dm_exec_query_plan(TBL2.plan_handle) AS TBL4 WHERE TBL3.text IS NOT NULL OR TBL4.query_plan IS NOT NULL ORDER BY 3 DESC; Select * from @Table2'') at [' + @server + ']'       PRINT @cmd       INSERT INTO @LogTable                   (cssionid,                    callocmb,                    cdeallocmb,                    ctext,                    cstatement)       EXEC(@cmd)       UPDATE @LogTable       SET    cservername = @server       WHERE  cservername IS NULL       FETCH next FROM c1 INTO @server   END CLOSE c1 DEALLOCATE c1 SELECT * FROM   @LogTable ``` ## Author Introduction: Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including [recover Outlook](https://www.datanumen.com/outlook-repair/) and excel recovery software products. For more information visit [www.datanumen.com](https://www.datanumen.com/) Share Now: - - - - - - - - [← Previous post](https://www.datanumen.com/blogs/batch-empty-deleted-items-folders-outlook-email-accounts/) [Next post →](https://www.datanumen.com/blogs/quickly-export-total-count-items-outlook-folder-excel/) Comments are closed. ![Data Recovery Blog]() Follow or like us on Facebook, LinkedIn and Twitter to get all promotions, latest news and updates on our products and company. - - - ### Featured Products - [DataNumen Outlook Repair](https://www.datanumen.com/outlook-repair/) - [DataNumen SQL Recovery](https://www.datanumen.com/sql-recovery/) - [DataNumen Exchange Recovery](https://www.datanumen.com/exchange-recovery/) - [DataNumen Access Repair](https://www.datanumen.com/access-repair/) - [DataNumen Excel Repair](https://www.datanumen.com/excel-repair/) - [DataNumen PDF Repair](https://www.datanumen.com/pdf-repair/) - [DataNumen Word Repair](https://www.datanumen.com/word-repair/) - [DataNumen Zip Repair](https://www.datanumen.com/zip-repair/) ### Popular posts ```json {"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.datanumen.com\/blogs\/find-reason-tempdb-full-sql-server\/#article","isPartOf":{"@id":"https:\/\/www.datanumen.com\/blogs\/find-reason-tempdb-full-sql-server\/"},"author":{"name":"AuthorCCW","@id":"https:\/\/www.datanumen.com\/blogs\/#\/schema\/person\/33ded6c47c77587f9158d99f05e23957"},"headline":"How to Find the Reason Why TempDB is Full in Your SQL Server","datePublished":"2017-06-02T01:32:07+00:00","dateModified":"2025-09-29T17:39:55+00:00","mainEntityOfPage":{"@id":"https:\/\/www.datanumen.com\/blogs\/find-reason-tempdb-full-sql-server\/"},"wordCount":331,"commentCount":1,"publisher":{"@id":"https:\/\/www.datanumen.com\/blogs\/#organization"},"image":{"@id":"https:\/\/www.datanumen.com\/blogs\/find-reason-tempdb-full-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2017\/06\/tempdb.jpg","articleSection":["SQL Server Solutions"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.datanumen.com\/blogs\/find-reason-tempdb-full-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.datanumen.com\/blogs\/find-reason-tempdb-full-sql-server\/","url":"https:\/\/www.datanumen.com\/blogs\/find-reason-tempdb-full-sql-server\/","name":"How to Find the Reason Why TempDB is Full in Your SQL Server","isPartOf":{"@id":"https:\/\/www.datanumen.com\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.datanumen.com\/blogs\/find-reason-tempdb-full-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/www.datanumen.com\/blogs\/find-reason-tempdb-full-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2017\/06\/tempdb.jpg","datePublished":"2017-06-02T01:32:07+00:00","dateModified":"2025-09-29T17:39:55+00:00","description":"If you are using a script to track temp DB size, append the script from this article to identify the root cause for the temp DB filling.","inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.datanumen.com\/blogs\/find-reason-tempdb-full-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.datanumen.com\/blogs\/find-reason-tempdb-full-sql-server\/#primaryimage","url":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2017\/06\/tempdb.jpg","contentUrl":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2017\/06\/tempdb.jpg","width":204,"height":204,"caption":"Tempdb"},{"@type":"WebSite","@id":"https:\/\/www.datanumen.com\/blogs\/#website","url":"https:\/\/www.datanumen.com\/blogs\/","name":"Data Recovery Blog","description":"Discuss every aspect of data recovery","publisher":{"@id":"https:\/\/www.datanumen.com\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.datanumen.com\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.datanumen.com\/blogs\/#organization","name":"Data Recovery Blog","url":"https:\/\/www.datanumen.com\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.datanumen.com\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2019\/02\/logowithouttext.jpg","contentUrl":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2019\/02\/logowithouttext.jpg","width":296,"height":397,"caption":"Data Recovery Blog"},"image":{"@id":"https:\/\/www.datanumen.com\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataNumen","https:\/\/x.com\/DataNumen","http:\/\/www.linkedin.com\/company\/datanumen","https:\/\/myspace.com\/datanumen\/","http:\/\/www.pinterest.com\/datanumen\/","https:\/\/www.youtube.com\/channel\/UCVIIuuKfUBHSJauUaVP13tw"]},{"@type":"Person","@id":"https:\/\/www.datanumen.com\/blogs\/#\/schema\/person\/33ded6c47c77587f9158d99f05e23957","name":"AuthorCCW","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3516ed90fc242780d3947a989f895bc46e70c3e83485d30f2b457a007906aa58?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/3516ed90fc242780d3947a989f895bc46e70c3e83485d30f2b457a007906aa58?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3516ed90fc242780d3947a989f895bc46e70c3e83485d30f2b457a007906aa58?s=96&d=mm&r=g","caption":"AuthorCCW"},"sameAs":["https:\/\/www.datanumen.com\/"]}]} ```