tempdb creation in minimal configuration mode

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

tempdb creation in minimal configuration mode

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-04-04 : 18:58:43

Hi,

In SQL Server 2005, At instance level i have changed my default database settings as below

Data: D:\sqldata
Log: E:\sqldata

Also, i have changed the location of tempdb to F:\ drive for better manageability purpose.

Now i have two questions regarding these locations

1. If i start the sql server normally, then tempdb is getting created in F:\ drive as expected. I believe, the locations of the tempdb is taken from master database.

2. If i start the sql server in minimally configuration mode i.e -f, now tempdb is getting created in default database settings location.
Why?

I think, here in this case the locations are read from registry and not the master database. please correct me if am wrong.

Also,in default database settings i said to create the .mdf file in "D:\sqldata" and .ldf to "E:\sqldata".
If that is true,it has to create the tempdb .mdf in D:\sqldata and temp.ldf in E:\sqldata but as per my observation both mdf and ldf are getting created in D:\sqldata. Why it is doing like that, is that a bug in sql server??

Thanks in Advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-04 : 19:41:53

1. That's not true that the location is from the master database. For 2005 and lower, it'll go wherever the system databases are put, however you can then move tempdb to a different location that's different than the other system databases.
2. Well it's a minimal configuration. If you want a better answer, you'll have to ask Microsoft.

It's not a bug. Default database settings are not settings for tempdb. If you want to move the ldf to a different location, then use the tempdb procedure here: http://support.microsoft.com/kb/224071

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-04-04 : 20:06:17

Thanks Tara.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources