3

I want to alter tempdb as such (i am increasing the size)

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp3', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp4', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 10240KB )
GO

Will this require me to restart SQL server?

3
  • Are you increasing or decreasing the size? Commented Oct 10, 2019 at 14:35
  • Increasing the size
    – jack
    Commented Oct 10, 2019 at 14:37
  • Increasing wont require restart but make sure that autogrowth is no in %.
    – Kin Shah
    Commented Oct 10, 2019 at 14:55

1 Answer 1

6

Increasing the size does not require a restart.

A small example on my dev environment

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 4096MB )
GO
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 4096MB )
GO

From:

enter image description here

To:

enter image description here


If you are decreasing the size with MODIFY FILE it would require a restart. If you want to decrease the size immediately, you would have to start shrinking (not ideal).

1
  • Does the TempDB filecount increase while staying at the same filesize for current files require restart?
    – Sranda
    Commented Sep 5, 2023 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.