patternsqlMinor
Does increasing the tempdb file size require a restart
Viewed 0 times
thefiletempdbrestartsizeincreasingdoesrequire
Problem
I want to alter tempdb as such (i am increasing the size)
Will this require me to restart SQL server?
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 )
GOWill this require me to restart SQL server?
Solution
Increasing the size does not require a restart.
A small example on my dev environment
From:
To:
If you are decreasing the size with
If you want to decrease the size immediately, you would have to start shrinking (not ideal).
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 )
GOFrom:
To:
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).
Code Snippets
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 )
GOContext
StackExchange Database Administrators Q#250765, answer score: 6
Revisions (0)
No revisions yet.