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

Does large tempdb file size have impact on sql server startup time?

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

Problem

Sql server tempdb data and log initial file size is configurable.

Assuming I set it to large values (say 10GB) then will sql server take more time to start compared to if the data/log initial file size were small (100MB)?

I'm asking because during sql installation it says the larger the tempdb data file initial size the longer it takes for installation. Does this also apply to sql service startup?

Solution

It depends on the Instant File Initialization.
Tempdb is recreated all at once at each sql server restart.

To ensure fast tempdb (and other database too) creation, enable Instant File Initialization granting perform volume maintenance tasks to the sql server service account.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver16

However, for large log files, you have to wait its creation based on your disks performance, as log files are not affected by Instant File Initialization

Context

StackExchange Database Administrators Q#317743, answer score: 4

Revisions (0)

No revisions yet.