Tempdb expand

[RGuardado]

Hello:

I'm using SQLServer 6.5 sp3. Right now I need to increase the size of tempdb but I dont know
how.
By default this db is localized in the master db with 2Mb, but I want to resize the tempdb with
300Mb of data and 200Mb of log, this 'cause somebody says that his application needs that
size in tempdb.

How can I expand my tempdb without all the expansion goes to the logs, I mention this 'cause
each time I expand tempdb all the increase goes to log part and the data remains with 2Mb.
Is correct to have this sizes(300Mb data- 200Mb log) ?

Thanks

[Gregory]

1) you don't want to create separate device for tran.log for tempdb
2) you need to create a new device , just for tempdb, ...let's say temdb_device (for example), amke 500M size

3) now, you need to make sure that none of your devices set as default device
(you can check device-by-device in enterprise manager under database devices, or you can simple run sp_helpdevice in query tool window - look under description column - make sure it doesn't say 'default' for any of your database devices. if you're using enterprise manager - make sure
none of your database devices have 'Default Device' check box checked.it should be unchecked for all databases devices)....

4) now, goto SQL Server configuration settings - you need to change
'tempdb in RAM' setting to 2 (to allocate 2M for tempdb in RAM)

5) restart SQL Server

6) run sp_helpdb , and sp_helpdevice - make sure it shows that tempdb in RAM,
not on master device !

7) now, set temdb_device (remeber device you created in step 1?) as a default device

8) goto SQL Server configuration settings - change setting 'tempdb in RAM' back to 0 (zero)

9) restart SQL Server .

10) run sp_helpdb/ sp_helpdevice to make sure that tempdb database is on tempdb_device .

hope, it works.mail me if any problems/questions


------------
RGuardado at 3/11/99 6:44:30 PM

Hello:

I'm using SQLServer 6.5 sp3. Right now I need to increase the size of tempdb but I dont know
how.
By default this db is localized in the master db with 2Mb, but I want to resize the tempdb with
300Mb of data and 200Mb of log, this 'cause somebody says that his application needs that
size in tempdb.

How can I expand my tempdb without all the expansion goes to the logs, I mention this 'cause
each time I expand tempdb all the increase goes to log part and the data remains with 2Mb.
Is correct to have this sizes(300Mb data- 200Mb log) ?

Thanks