locked
TEMP DB RRS feed

  • Question

  • Hi All,

    I found the following issue on SQL server log please explain why this happens also help me to troubleshoot this issue.

    Autogrow of file ‘tempdev’ in database was cancelled by user or timed out after 170 milliseconds. Use ALTER Databasae to set a smaller FILEGROWTH value for this file or to explicitly set a new file size

    Thanks


    DBA

    Wednesday, November 26, 2014 3:36 PM

Answers

  • If you have 50 GB then the error would have occurred prior to that.

    "Initial size of Data file: 493MB and log size of the file 32MB, both are setting 10% of autogrowth option

    Also the TempDB size configured 50GB, it's totally empty."

    I didnt quite understand this. How is tempdb configured for 50 GB when initial data file is 493 MB? I assume that means there was autogrowth. I would recommend to set it initially to a decent value , rather than 493 MB which doesnt look to be good.

    Also change the autogrowth to MB and set it to 250 (as a starter).


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by dbadays Wednesday, November 26, 2014 4:32 PM
    Wednesday, November 26, 2014 4:21 PM

All replies

  • This happens because there is no space in tempdb for data growth. Some queries were using tempdb for operations and it needed more space.

    Autogrowth was kicked off and that operation was stopped. I would recommend you to manually increase the size of tempdb such that frequent autogrowth is not required.

    Make sure that the autogrowth is not in percentage and is in MB's (start with 250 MB and tune it). Also check instant file initialization is enabled which will speed the growth process for data files.

    Check this articles

    http://support.microsoft.com/kb/2091024

    http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com



    • Edited by Ashwin Menon Wednesday, November 26, 2014 3:48 PM
    Wednesday, November 26, 2014 3:44 PM
  • What version of SQL server are you using?

    Also, provide some info about your tempDb. Size of drive on which it resides? ; tempdb auto growth setting etc... 

    Have you tried setting filegrowth to lower value than existing one? 

    Wednesday, November 26, 2014 3:46 PM
  • Hi Mudit,

    The configuration settings as below

    Initial size of Data file: 493MB and log size of the file 32MB, both are setting 10% of autogrowth option

    Also the TempDB size configured 50GB, it's totally empty.

    I don't have any idea why the issue happens.

    Thank you


    DBA

    Wednesday, November 26, 2014 3:58 PM
  • Hi Ashwin,

    The TempDB having 50GB of space available, any recommendations and considerations to change the tempDB settings?


    DBA

    Wednesday, November 26, 2014 4:04 PM
  • Did you try Ashwin's recommendations.

    It's good practice to set to autogrowth in MB's. 

    Create one data files per logical processors.Set the initial file size to  a large value so that it can balance the contention.

    Please refer the below link

    http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

    --Prashanth

    Wednesday, November 26, 2014 4:10 PM
  • Hi Prashanth,

    Currently I'm Using Production Server, If I change the tempDB configuration does it impact on the server?


    DBA

    Wednesday, November 26, 2014 4:18 PM
  • It can be done online. You are just increasing the size and adding the files, if required.

    --Prashanth

    Wednesday, November 26, 2014 4:21 PM
  • If you have 50 GB then the error would have occurred prior to that.

    "Initial size of Data file: 493MB and log size of the file 32MB, both are setting 10% of autogrowth option

    Also the TempDB size configured 50GB, it's totally empty."

    I didnt quite understand this. How is tempdb configured for 50 GB when initial data file is 493 MB? I assume that means there was autogrowth. I would recommend to set it initially to a decent value , rather than 493 MB which doesnt look to be good.

    Also change the autogrowth to MB and set it to 250 (as a starter).


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by dbadays Wednesday, November 26, 2014 4:32 PM
    Wednesday, November 26, 2014 4:21 PM
  • Dbadays,

    Ashwin's explanation is right. You can try setting up growth in MBs or GBs , depending upon tempDB behavior. For some heavily used databases, I set them up with increment of 1GB or so, and keep monitoring disk space and activity regularly. 

    best practices  say set db growth to fixed size , rather than percent, for both data and log files. 

    Thanks!

    Wednesday, November 26, 2014 4:24 PM
  • Hi Ashwin Thanks for your effort, Please give me your valuable recommendations on the tempDB file values.

    Thanks


    DBA

    Wednesday, November 26, 2014 4:32 PM