Results 1 to 4 of 4

Thread: TempDB

  1. #1
    Join Date
    Jul 2009
    Posts
    23

    Unanswered: TempDB

    Hi,

    I am running an INSERT statement (the table is built with 1 billion records). The problem is the TempDB grows to over 600 GB and fills the disk prior to completion of the query executing.
    I checked the tempDB and details as follows;

    Code:
    Recovery model: Simple 
    
    Logic Name: tempdev 
    Initial size: 8MB 
    Autogrowth: By 10 percent, unrestricted growth 
    
    Logic Name: templog 
    Initial size: 1MB 
    Autogrowth: By 10 percent, unrestricted growth
    Code:
    INSERT INTO dbo.EmisEvent (EventGuidDigest,PatientID,ConsultationID,StaffID,EventDate,EventType,MedicalCodeID,Value,Unit,Abnormal)
    SELECT EventGuidDigest,PatientID,ConsultationID,StaffID,EventDate,EventType,MedicalCodeID,Value,Unit,Abnormal
    FROM dbo.EmisEventExtraction WITH (tablock)
    Please advise if I need to do anything to control the size of the tempDB from growing out of control and utilising the entire disk space..

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Assuming that the dbo.EmisEvent table is NOT in tempdb, then the example statement that you posted should not use tempdb.

    I suspect that there may be a trigger or something else causing the problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2016
    Posts
    24
    Provided Answers: 1

    identify the cause then shrink or restart

    Execute the following query to check what is causing the tempdb to grow
    HTML Code:
    PRINT '-- sys.dm_db_file_space_used'
    select CONVERT (varchar(30), @runtime, 121) AS runtime, SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
    SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
    SUM (version_store_reserved_page_count)*8 as version_store_kb,
    SUM (unallocated_extent_page_count)*8 as freespace_kb,
    SUM (mixed_extent_page_count)*8 as mixedextent_kb
    FROM sys.dm_db_file_space_usage
    
    PRINT '-- sys.dm_db_session_file_usage'
    select top 10 CONVERT (varchar(30), @runtime, 121) AS runtime, * FROM sys.dm_db_session_space_usage 
    ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC
    
    PRINT '-- sys.dm_db_task_space_usage'
    SELECT top 10 CONVERT (varchar(30), @runtime, 121) AS runtime, * FROM sys.dm_db_task_space_usage
    ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC
    Once you eliminate the cause, then you can shrink or restart the sql server services

  4. #4
    Join Date
    Jan 2016
    Posts
    24
    Provided Answers: 1
    One of the most common causes for this is a complex query that makes heavy usage of worktables during processing or makes heavy use of Common Table Expressions that reference each other in a recursive fashion.

    To prevent tempdb to go out of space

    • Set tempdb to auto grow.
    • Set it’s initial size reasonably
    • Ensure the disk has enough free space
    • Batch larger and heavy queries
    • If possible put tempdb on its separate disk.
    • Try to write efficient code for all stored procedures, cursors etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •