none
Is any major impact in production SQL2014, if templog Autogrowth changed to Unlimited to Limited

    Question

  • Hi All,

    Is any major impact in production SQL2014, if templog Autogrowth changed to Unlimited to Limited

    Thanks,

    Jayaprakash




    • Edited by JPSQL Wednesday, June 27, 2018 5:16 AM
    Wednesday, June 27, 2018 4:23 AM

All replies

  • Why set Autogrowth? Set the auto-growth increment for the log file to something relatively small 

    Back to your question , if the size riches the limited size the error will be thrown

    The better way would be to use Perfmon to monitor the SQL:Database:Log Used (kb) counter to see when it is growing, and correlate that to other activity on the server.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Wednesday, June 27, 2018 5:38 AM
    Moderator
  • changed to Unlimited to Limited


    To To? Which is the "From" part?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, June 27, 2018 5:53 AM
    Moderator
  • Hi All,

    Is any major impact in production SQL2014, if templog Autogrowth changed to Unlimited to Limited

    If you limit tempdb growth and if it wants to grow beyond the limit SQL Server will just freeze or may even restart. This is risky

    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

    Thursday, June 28, 2018 7:38 AM
    Moderator
  • Hi All,

    Is any major impact in production SQL2014, if templog Autogrowth changed to Unlimited to Limited

    Don't rely on auto-growth. Pre-allocate your TempDB files. Autogrowth, for TempDB, is temporary anyway because when the instance is restarted, it'll go back to its configured size. If the size is limited, as others have said, the log wouldn't be able to grow beyond that number.

    Looking from a different angle- if you thoroughly test your application load etc in a lower environment and set the tempDB size appropriately, it may not even reach the limited size you've set up or run out of space. 

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Thursday, June 28, 2018 3:06 PM