T HERRERA) writes:
C.
>Yenzi) writes:
>>I have increased the size of my tempdb database to accomodate a large
>number
>>of users. I created a device file for the new portion and expanded
>tempdb
>>onto it. Now I have two questions and was wondering if anyone could
>help
>>1) How do I start SQL Server if I lose the second device file. (I
>removed it
>>on purpose from my test database and it will not start)
>>2) How can I completely move the tempdb database off of the master
>device so
>>it resides completely on its own device
>>Any help would be appreciated
>>Damian Yenzi
>>Federated Investors
>Damian:
>Dude ... It's pretty *y!
>>1) How do I start SQL Server if I lose the second device file. (I
>removed it
>>on purpose from my test database and it will not start)
>You have to rebuild your config block for the master DB. Use
>buildmaster to view all current config block setting and save it off
to
>another file. You'll need this information later to get back to your
>current configuration if something gets blown away for good again.
>Next, There is a switch that you add to the startup command that
allows
>you to start master DB only. I don't remember it because it's been a
>few years since I did something similar. You can do the research,
>though, in your documentation at work.
>Once you get the server up and running on master DB only, reconfigure
>allow updates to allow system table configuration. Now, PAY REAL
CLOSE
>ATTENTION TO WHAT YOU TYPE IN AT THE COMMAND LINE!!! Delete the row
in
>sysdevices that corresponds to the device you extended tempdb onto.
>Reconfig allow updates to not allow system table mods.
>Shutdown and restart normally.
>>2) How can I completely move the tempdb database off of the master
>device so
>Go into tempdb and use sp_dropsegment.
>Give me a shout if you have any questions!
>RogerH
Damian:
I would like to add a couple of other tid-bits.
1) I found the parameter used to start up your server. Use the -m
parm. It should set you up to where you don't even need to reconfig
the allow updates value.
2) I'm working on the assumption that the last device you added was
the tempdb device that was deleted from disk. I don't think that my
suggestions will work if you added devices after you added the tempdb
device. It seems that the low and high values in sysdevices are
inventoried as sql server starts up. If the values aren't correct, the
startup fails. Therefore ... Caveat: If the device being deleted from
sysdevices is not the last device added to the RDBMS, the low and high
values will have a gap during startup and CRASH the server.
3) You also need to delete a record from sysusages to avoid any tempdb
suspect errors. Since the tempdb dbid is always 2, look at all the
records in sysusages that have a dbid of 2. You'll see the allocations
to the master device and the device that was physically deleted from
disk. If you want to be positive, find the sysusage record that has a
vstart value the same as the sysdevices record low value that is to be
deleted.
let me know how it turns out for you!
RogerH
by