[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-09-04
6
Medium Priority
?
728 Views
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.
0
Comment
Question by:EddieIT
6 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 38365610
try this method
http://askmesql.blogspot.ca/2012/02/how-to-get-history-of-sql-scripts-that.html

or

SELECT  TOP 100
            [Object_Name] = object_name(st.objectid),
            creation_time,
            last_execution_time,
            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,
            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
                              END
                              - qs.statement_start_offset
                        ) /2
                  ) + 1
            ) as statement_text
FROM
            sys.dm_exec_query_stats qs
CROSS APPLY
            sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
            Object_Name(st.objectid) IS NOT NULL
            AND st.dbid = DB_ID()
ORDER BY
            db_name(st.dbid),
            total_worker_time / execution_count  DESC
0
 

Author Comment

by:EddieIT
ID: 38365673
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?
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 38365887
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.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:EddieIT
ID: 38365889
Can I get a qyery example?
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 38365937
A query example means?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38366004
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).
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

834 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