moving tempdb and starting db when tempdb is lost

moving tempdb and starting db when tempdb is lost

Post by Damian C. Yen » Fri, 03 May 1996 04:00:00



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

 
 
 

moving tempdb and starting db when tempdb is lost

Post by ROGER T HERRE » Mon, 06 May 1996 04:00:00



Quote: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!

Quote:>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.

Quote:>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

 
 
 

moving tempdb and starting db when tempdb is lost

Post by ROGER T HERRE » Tue, 07 May 1996 04:00:00



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

 
 
 

1. Another problem starting SQL Server after moving tempdb into RAM

I changed tempdb to run in RAM. I guess I gave it too much horsepower. now
I can't start SQLserver. I've tried the following but none work:

sqlservr -t3608 -f
sqlservr -f
sqlservr -f -c
sqlservr -d<master.dat location> -e<Log location> -T3608
sqlservr -c -m -T3609 -T4022

I run these from the FILE, RUN command line.

After I run these, I cannot start MSSQLserver (through Sql Server Manager).
The event log says Unable to move tempdb into RAM. Not enough RAM to move
tempdb (error 18118).

I thought that starting sqlservr with the -f switch would allow me to get
in and run the SP_configure command to reconfigure the tempdb.

Thanks in advance
Jim

2. Recordset is nothing after calling a Web database via RDS

3. SQL Server Agent service won't start after moving tempdb

4. Problem with Incremential Update in Cubes with Several Partitions

5. Move master db and tempdb?

6. 19207-NE-Omaha-ORACLE-Designer 2000-Developer 2000-Oracle Application Developers/Designers

7. Moving master db and tempdb ?

8. shrinking tempdb without tempdb existing

9. Performance/TempDB Config Issue: Sorting Queries and TempDB

10. Optimizing tempdb Performance, Using filegroups with tempdb