none
Full temp file on sql server on drive c when user call report RRS feed

  • Question

  • Hi everyone,

    I got problem temp file in sql server growth faster to make drive C full space during user calling report and task backup also execute on that time, but when i check on server resource (RAM, CPU) work properly.

    Anyone used to face issue like this?

    Friday, May 22, 2020 11:02 AM

All replies

  • Seems like you tempdb is on C drive and due to query it has filled up the drive. You should not put tempdb on C drive. What you can do is immediately add more space in C drive so that the query can complete and sql server comes out of freeze state.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, May 22, 2020 2:36 PM
  • It's not a good idea to locate the TempDB on system drive C; add an additional drive and move the TempDB to it.

    Then analyze the queries used by the report(s).


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, May 23, 2020 4:10 AM
  • You seem to have two not recommended settings

    1. Placing the TempDB files on C drives

    2. Leaving TempDB database max size to unlimited which has resulted in filling up the entire available free C drive.  

    Do you have any other drives on the system?

    If yes please move the TempDB file from its current location to another location. Below are the steps

    1. Identify the current TempDB location

    USE TempDB
    GO
    EXEC sp_helpfile
    GO

    2. Relocate TempDB files to another drive. In the below example it is to D drive.

    USE master
    GO
    ALTER DATABASE TempDB MODIFY FILE
    (NAME = tempdev, FILENAME = 'd:\datatempdb.mdf')
    GO
    ALTER DATABASE TempDB MODIFY FILE
    (NAME = templog, FILENAME = 'd:\datatemplog.ldf')
    GO

    3. Restart SQL Server. Keep in mind it will not take effect until you recycle SQL Server

    If you do not have any other drives and do not have the option of adding one.. then atleast set max size for tempdb files to an appropriate size so that it will not fill up the entire drive.

    After all this is done then start analyzing the queries hitting TempDB.. But you need to move TempDB out of C drive or atleast set a max limit for it before this.



    Sunday, May 24, 2020 10:15 PM