none
SQL Server changes that require a restart RRS feed

  • Question

  • I'm looking for an overview of which SQL Server changes require a restart. This is not about the parameters that are listed in the sys.configurations (here I know that a restart is required if the value of the column is_dynamic is 0). Rather, it is about other changes such as...

    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3

    (changes only takes effect after a restart)

    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 12

    (but I didn't have to restart here).

    ...and whatever else there is. So far I couldn't find any documentation about what requires a restart.
    Thank you and kind regards

    Oliver.


    Tuesday, October 22, 2019 12:55 PM

All replies

  • Patching and moving/altering system databases

    Service account changes

    Trace Flags that you want to be startup parameters

    Any startup parameter changes

    Enable/Disable Instant File Initialization

    top of my head...


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Tuesday, October 22, 2019 1:01 PM
  • Hi Oliver!

    Can you check this link -> https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-configuration-options-sql-server?redirectedfrom=MSDN&view=sql-server-ver15

    Here you have all the options that needs restart or not.

    Hope this helps!

    Tuesday, October 22, 2019 1:07 PM
  • Kevin installing SQL Server SP/CU does not necessarily requires a service restart but yes it is suggested to do it. MS will tell you when restart is required it happens only when files were locked and MS installation could not get lock on it.

    Oliver there is unfortunately not document from MS which says list of changes which requires restart. You may have to go through lot of docs to collate these.


    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


    Wednesday, October 23, 2019 7:16 AM
  • Patching and moving/altering system databases

    Service account changes

    Trace Flags that you want to be startup parameters

    Any startup parameter changes

    Enable/Disable Instant File Initialization

    top of my head...


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Thanks for the overview. Do you know a website that describes all this in more detail?
    Wednesday, October 23, 2019 7:23 AM
  • Hi Oliver!

    Can you check this link -> https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-configuration-options-sql-server?redirectedfrom=MSDN&view=sql-server-ver15

    Here you have all the options that needs restart or not.

    Hope this helps!

    Thanks for your answer. I think the web page only describes the restarts related to changes to parameters contained in sys.configurations. I'm looking for a documentation (webpage) when else a restart is required (like for example in my description when changing the audit level with xp_instance_regwrite).
    Wednesday, October 23, 2019 7:23 AM
  • Kevin installing SQL Server SP/CU does not necessarily requires a service restart but yes it is suggested to do it. MS will tell you when restart is required it happens only when files were locked and MS installation could not get lock on it.

    Oliver their is unfortunately not document from MS which says list of changes which requires restart you may have to go through of docs to collate these.


    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

    Thanks for the feedback. I had hoped that someone would know of a website where it was put together.

    Wednesday, October 23, 2019 7:30 AM
  • Hi Oliver-T,

     

    It seems that no one has sorted out these. The only one that can be found is the configuration options in sys.configuration. You can 

    Select * from sys.configurations .If the value in is_dynamic column for any configuration is 1, that means the change is dynamic in nature and it doesn’t require SQL Server Service restart to take that change into effect, otherwise if the value is 0 that means restart is required.

    Unfortunately, you may need to summarize whether other configurations require a restart to take effect.

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, October 23, 2019 9:09 AM
  • Thanks for the overview. Do you know a website that describes all this in more detail?

    I do not, sorry.  Maybe I'll ask the community and blog them myself some day...

    Kevin


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.


    • Edited by KevinBHill Wednesday, October 23, 2019 12:03 PM
    Wednesday, October 23, 2019 12:03 PM