tempDB space needed

Posted on 2012-09-18
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
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    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
    also instead of % growth, you can set a growth of 200MB or so; that way it wont allocate 10% everytime.
    LVL 15

    Assisted Solution

    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.
    LVL 3

    Accepted Solution

    You could also use the following:

    use tempdb


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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

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

    Suggested Solutions

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    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…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 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

    12 Experts available now in Live!

    Get 1:1 Help Now