SQL 2012 Tempdb fulling Up Disk Space

I understand the tempdb is suppose to growth to accomidate incoming request and a poorly setup tempdb and impact performance. We are having a problem with only DB had host a very write intensive application and is cause the tempdb to take up all space and we need to stop and start the SQL service daily. If I disable autogrowth on the tempdb for the short term until more disk array besides performace what else will suffer?
LVL 21
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
If I disable autogrowth on the tempdb for the short term until more disk array besides performace what else will suffer?
The application will stop if it can't make any operation in tempdb.
Best practices says that tempdb should be isolated in his own disk so I recommend you to add an additional disk and move tempdb to there so it won't consume space for the others databases.
compdigit44Author Commented:
But we do not have any more space and our additional array is due in the end of the week.

So the more chatty a application is the large the tempdb will get? Out is averaging around 100GB
Vitor MontalvãoMSSQL Senior EngineerCommented:
But we do not have any more space and our additional array is due in the end of the week
I'm sorry to say but that was a bad planning.

So the more chatty a application is the large the tempdb will get?
No but depends how often an application need to use tempdb. There are big applications that don't need to use that much temporary objects and small applications that creates a lot of temporary objects so it really depends on how it was developed.

By the way, do you have maintenance tasks running periodically? Specially reindex tasks? These kind of tasks can consume a lot of tempdb.
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

compdigit44Author Commented:
I do have a maintenance task run daily...

I have never see I tempdb grow so fast before...
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check the definitions for Reindex and Rebuild tasks and verify if the check box to use tempdb is checked.
Also how often those tasks runs?
Scott PletcherSenior DBACommented:
Look at tempdb and see what object(s) are taking up so much space.  That might point you back to what is causing the problem, or at least it will narrow it down considerably.
compdigit44Author Commented:
How can I look for the objects?
Scott PletcherSenior DBACommented:
I'm not going to worry about showing xml or other special indexes, as that should not be relevant to tempdb.
USE tempdb

DECLARE @has_clus_index_show_yes bit
SET @has_clus_index_show_yes = 0

    SCHEMA_NAME(o.schema_id) AS Schema_Name, 
    o.name AS Table_Name,
    Table_MB, Table_Rows,
    Table_Plus_Indexes_MB, Table_Plus_Indexes_Rows,
    CASE WHEN Has_Clus_Index = 0 THEN 'No' ELSE CASE WHEN @has_clus_index_show_yes = 1 THEN 'Yes' ELSE '' END END AS Has_Clus_Index,
    FILEGROUP_NAME(fg_min) + CASE WHEN fg_min = fg_max THEN '' ELSE '/' + FILEGROUP_NAME(fg_max) END AS Filegroup,
    CASE WHEN data_compression_max = 0 THEN '' ELSE
        CASE data_compression_min WHEN 0 THEN 'none' WHEN 1 THEN 'ROW' WHEN 2 THEN 'PAGE' ELSE '?' END +
        CASE WHEN data_compression_max = data_compression_min THEN '' ELSE '/' +
        CASE data_compression_max WHEN 0 THEN 'none' WHEN 1 THEN 'ROW' WHEN 2 THEN 'PAGE' ELSE '?' END END END AS Compression,
    o.create_date AS Table_Create_Date
        dps2.object_id, /*OBJECT_NAME(dps2.object_id),*/
        CAST(SUM(/*CASE WHEN dps2.index_id IN (0, 1) THEN */dps2.reserved_page_count /*ELSE 0 END*/) / 128.0 AS decimal(9, 2)) AS Table_MB,
        SUM(CASE WHEN dps2.index_id IN (0, 1) THEN row_count ELSE 0 END) AS Table_Rows,
        CAST(SUM(dps2.reserved_page_count) / 128.0 AS decimal(9, 2)) AS Table_Plus_Indexes_MB,
        SUM(dps2.row_count) AS Table_Plus_Indexes_Rows,
        COUNT(DISTINCT CASE WHEN dps2.index_id > 0 THEN dps2.index_id END) AS Total_#_Of_Indexes,
	    MAX(CASE WHEN dps2.index_id = 1 THEN 1 ELSE 0 END) AS [Has_Clus_Index] ,
	    MAX(p2.data_compression_min) AS data_compression_min,
	    MAX(p2.data_compression_max) AS data_compression_max,
	    MAX(p2.fg_min) AS fg_min,
	    MAX(p2.fg_max) AS fg_max
    FROM sys.dm_db_partition_stats dps2 WITH (NOLOCK)
            p3.object_id, MAX(p3.rows) AS rows_max,            
            MIN(p3.data_compression) AS data_compression_min, MAX(p3.data_compression) AS data_compression_max, 
            MAX(au3.fg_min) AS fg_min, MAX(au3.fg_max) AS fg_max
        FROM sys.partitions p3 WITH (NOLOCK)
            SELECT au4.container_id, au4.type, MIN(au4.data_space_id) AS fg_min, MAX(au4.data_space_id) AS fg_max
            FROM sys.allocation_units au4 WITH (NOLOCK)
            GROUP BY au4.container_id, au4.type
        ) AS au3 ON
            au3.container_id = CASE WHEN au3.type = 2 THEN p3.partition_id ELSE p3.hobt_id END            
        GROUP BY p3.object_id
    ) AS p2 ON
        p2.object_id = dps2.object_id
) AS dps
INNER JOIN sys.all_objects o WITH (NOLOCK) ON
    o.object_id = dps.object_id
    o.name NOT LIKE 'sys%'
	Table_MB DESC,
	--Table_Plus_Indexes_MB DESC, --from largest to smallest total table plus indexes size
    Schema_Name, Table_Name

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
compdigit44Author Commented:
I am not sure 100% how to read the results but all table size are around 0.00 - 0.05MB
Can you try to change TEMPDB recovery model to simple.
compdigit44Author Commented:
I will but also want to run your script if I am able when the tempdb is maxed out in size to see where all of the space is going..
compdigit44Author Commented:
I just check and the tempdb is already set to simple mode recovery model
Vitor MontalvãoMSSQL Senior EngineerCommented:
Tempdb is always on Simple Recovery Model. Even if that was possible it would only affect the transaction log anyway.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.