Determining the initial size of TempDB?

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I know it's early but (my upfront way of saying this may be crazy) but...

1. I know that tempdb is re-created everytime that SQL is restarted.

2. Doesn't it use model as a reference so it would be the same size?

Good Hunting!

David Poole-249495 Ten Centuries Points: 1106 More actions · Answer 2

Nope, it doesn't inherit its size from MODEL.

Its default initial size is 8MB where as MODEL is 2MB.

If I do the following

1. Restart SQL Server

2. ALTER DATABASE TempdB MODIFY FILE (NAME=tempdev , SIZE=16MB)

3. Perform some action that causes TempDB to grow beyond 16Mb (a large sort will do it)

4. Restart the MSSQLSERVER service

TempDB resizes to 16Mb. Clearly it is getting that setting from somewhere but I don't know where!

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

The initial specifications for tempdb data and log are retained in table master.dbo.sysaltfiles.

select dbid, name, size as SizePages

, Size * 8 / 1024 as SizeMb

from sysaltfiles where dbid = 2

go

SQL = Scarcely Qualifies as a Language