SQL 2008 TEMPDB is filling up with over 20GB in space

Posted on 2012-09-04
Last Modified: 2012-09-11
Hello Experts, I'm running SQL 2008 server with 2 different VB/.NET applications and 14 databaases (7 per application). Since 3 weeks back, due to some specific process/query my temp DB has begun to grow. So when I reboot the serevr, I have 30 GB available, after a specific process is executed, I'm left with 20MB?!?!?!

I need to find out which DB / Query is writing to the temp MB and making it grow to thsi size without deleting the temp records, please advise.

Thank you in advance for your help.
Question by:EddieIT
    LVL 75

    Accepted Solution

    try this method


    SELECT  TOP 100
                [Object_Name] = object_name(st.objectid),
                total_cpu_time = total_worker_time / 1000,
                avg_cpu_time = (total_worker_time / execution_count) / 1000,
                min_cpu_time = min_worker_time / 1000,
                max_cpu_time = max_worker_time / 1000,
                last_cpu_time = last_worker_time / 1000,
                total_time_elapsed = total_elapsed_time / 1000 ,
                avg_time_elapsed = (total_elapsed_time / execution_count) / 1000,
                min_time_elapsed = min_elapsed_time / 1000,
                max_time_elapsed = max_elapsed_time / 1000,
                avg_physical_reads = total_physical_reads / execution_count,
                avg_logical_reads = total_logical_reads / execution_count,
                SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                                  CASE statement_end_offset
                                        WHEN -1 THEN DATALENGTH(st.text)
                                        ELSE qs.statement_end_offset
                                  - qs.statement_start_offset
                            ) /2
                      ) + 1
                ) as statement_text
                sys.dm_exec_query_stats qs
                sys.dm_exec_sql_text(qs.sql_handle) st
                Object_Name(st.objectid) IS NOT NULL
                AND st.dbid = DB_ID()
                total_worker_time / execution_count  DESC

    Author Comment

    The above came back with a blank result. Maybe because I rebooted teh SQL services in order to empty out some space. Any way to retrieve the history information?
    LVL 25

    Expert Comment

    No. Not now. Whenever you reboot or restart the service, the tempdb is created afresh. So, everything will be gone. You can schedule a job that runs the query posted by the expert and stores data into a table. You can analyze the table later.

    Author Comment

    Can I get a qyery example?
    LVL 25

    Expert Comment

    A query example means?
    LVL 68

    Expert Comment

    You will simply have to get more disk space for tempdb.

    Tempdb is used for all sorts of things by SQL.  It will take you time to track down tempdb space usage.  

    30GB is so tiny, in modern terms, that you probably cannot realistically expect to limit tempdb to that size nowadays.

    And you should go ahead and pre-allocate tempdb, including the log, to the size it will need, because of the overhead of it growing while SQL is running.

    Also, change the increment to a fixed amount that is not too small (such as 50MB or 100MB, rather than 1-10MB).

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now