Can't drop device from tempdb

[Richard P Moxley]

Anybody know how to drop a device from tempdb?

I'm using Sybase 10.0.2 on an SGI Onyx running IRIX 5.3.
After running out of space in the system segment of tempdb,
I added a large file as a device and extended the system
segment to use it.

Now I want to undo all of this, but the server won't let
me. I've used sp_dropsegment to drop the system segment
from the device, but it still won't let me drop the device
because it claims tempdb is still using it. I can't drop
tempdb because the system owns it. So now what?

Here's how I added the device:

disk init name="temp1", physname="/mydisk/temp1",
vdevno=2, size=1024000

alter database tempdb on temp1=2000

sp_extendsegment system, tempdb, temp1

Then, I tried to back it out with:

sp_dropsegment system, tempdb, temp1

The server was ok with this, but then I did:

sp_dropdevice temp1

And the server says "Device is being used by a database.
You can't drop it." It shows tempdb as still using it.
And it won't let me drop tempdb.

If anyone has any suggestions, I'd really appreciate it.
I want my 2000 MB back!

Cheers,
R

[Grep Awkbourne a.k.a. Geoffrey Ogborn]

rmo...@osf1.gmu.edu (Richard P Moxley) wrote:
>Anybody know how to drop a device from tempdb?
>
>I'm using Sybase 10.0.2 on an SGI Onyx running IRIX 5.3.
>After running out of space in the system segment of tempdb,
>I added a large file as a device and extended the system
>segment to use it.
>

Cool!

sp_dropsegment won't work. Here is what we do, but be very careful.

Since tempdb gets rebuilt each time the server is rebooted all you need
to do is delete from sysusages the LAST record from associated with tempdb
(dbid 2). Don't try deleting a record in the middle or your dead.

Here is how I would do it.

1> sp_configure allow,1
2> go
Configuration option changed. Run the RECONFIGURE command to install.
(return status = 0)

1> reconfigure with override
2> go

1> select db_name(2)
2> go
------------------------------
tempdb

1> select * from sysusages where dbid = 2
2> go
dbid segmap lstart size vstart pad unreservedpgs
------ ----------- ----------- ----------- ----------- ------ -------------
2 7 0 1024 2564 NULL 680
2 7 1024 102400 50331648 NULL 102400

delete sysusages
where dbid = 2
and lstart = 1024 /* BE SURE TO PUT YOUR VALUES HERE */
and size = 102400
and vstart = 50221648
go

/* I basically cover the entire record just to be sure I don't delete any */
/* other records */

shutdown (with nowait)
go

REBOOT SQL SERVER.

At this point your device should come up totally unused.

Hope this helps. Send me email if you get stuck and I will be glad to lend
a hand.

Geoff Ogborn
ogb...@kaos.corp.sgi.com

[Wendy Perry]

Check the sysusages and syssegments table for tempdb and your particular
device and segment. The only way to it may to be manually delete the
entries from these tables. I would recommend being extremely cautious if
taking this route and running the delete within a transaction so that you
can roll back if the expected number of rows is not deleted. I have
removed tempdb from devices using this method and have had no problems.

[savilc...@gmail.com]

thanx bro it worked