HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Does increasing the tempdb file size require a restart

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thefiletempdbrestartsizeincreasingdoesrequire

Problem

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?

Solution

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:

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

Context

StackExchange Database Administrators Q#250765, answer score: 6

Revisions (0)

No revisions yet.