tempDB space needed

Posted on 2012-09-18
Medium Priority
Last Modified: 2012-10-03
Hello experts
I need to check I have enough space on my tempdb. I was kind of running out of space and I recently remodev many old backups file from the drive.. This released like an 80 G free space. I was wondering how would I know that I have enough free space on my tempdb.
The outgrowth is set to 10%

Thank you
Question by:shpresa
  • 2
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 668 total points
ID: 38410899
the best way is to monitor the disk space; if you have enough free space, you will be safe.
Like the other user databses, tempdb will also be groowing over time.
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38410901
also instead of % growth, you can set a growth of 200MB or so; that way it wont allocate 10% everytime.
LVL 15

Assisted Solution

Anuj earned 664 total points
ID: 38410950
Another thing you should monitor is like, whether your index rebuilds are using sort in tempdb options or not, if they are don't get surprised to see the tempdb growth, like wise CHECKDB.

Here is the script that i used to monitor tempdb space usage for the active session
SELECT sys.dm_exec_sessions.session_id AS [SESSION ID],
DB_NAME(database_id) AS [DATABASE Name],
HOST_NAME AS [System Name],
program_name AS [Program Name],
login_name AS [USER Name],
cpu_time AS [CPU TIME (in milisec)],
total_scheduled_time AS [Total Scheduled TIME (in milisec)],
total_elapsed_time AS    [Elapsed TIME (in milisec)],
(memory_usage * 8)      AS [Memory USAGE (in KB)],
(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)],
(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)],
(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)],
(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)],
CASE is_user_process
                     WHEN 1      THEN 'user session'
                     WHEN 0      THEN 'system session'
END   AS [SESSION Type], row_count AS [ROW COUNT]
FROM sys.dm_db_session_space_usage
INNER join sys.dm_exec_sessions
ON sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

Open in new window

You can also create alerts that triggers based on the free space available in disk where tempdb is located. For example, if your tempdb resides in F:\ and if the F:\ and when free space available in the drive is less than 10%, then the SQL Server Alerts will notify the DBA.

Accepted Solution

dave_tiller earned 668 total points
ID: 38414058
You could also use the following:

use tempdb


This will give the information about the TempDB as far as used and unused space.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

840 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