2

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?

  • Are you increasing or decreasing the size? – Randi Vertongen Oct 10 '19 at 14:35
  • Increasing the size – jack Oct 10 '19 at 14:37
  • Increasing wont require restart but make sure that autogrowth is no in %. – Kin Shah Oct 10 '19 at 14:55
5

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).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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