Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help - Reclaiming used space

Status
Not open for further replies.

grega

Programmer
Feb 2, 2000
932
GB
I've probably made a faux-pas here, but maybe someone can advise ... I'm running ASE11.9.2 on a test machine here, and have 5 devices configured (master, sysprocsdev) and my own ..

sdev1 - 1600 Mb
sdev2 - 1600 Mb
sdev3 - 300 Mb

I intended to increase the size of tempdb (currently the default 2Mb data+log on the master device) and add an additional 300Mb of data+log on sdev1, but I issued the wrong command ([tt]ALTER DATABASE TEMPDB LOG ON SDEV1=300[/tt]), which resulted in me creating 300Mb of log space on sdev1 ... not what I wanted at all.

In order to fix this, I did a [tt]sp_dropsegment logsegment, tempdb, sdev1[/tt] which seemed to work but I got the message

[tt]
WARNING: There are no longer any segments referencing device 'device'. This device will no longer be used for space allocation.
The procedure was successful, but the device is now unassigned and cannot be used for storing data or log information.
[/tt]

and the space has not been freed up. I eventually did issue the correct command ([tt]ALTER DATABASE TEMPDB ON SDEV2=300[/tt]) and now have 300Mb of data+log on sdev2, but would like to reclaim my space on sdev1.

Any suggestions, or is it too late?

Greg.
 
Greg,
I came across this posting today. You sure would've solved it by now. But, if you want to reclaim the space allocated to tempdb, the best way I suppose is to delete the corresponding entry in master..sysusages table for dbid=2 (tempdb) and then use 'alter database tempdb' so as to extend the db once again. Please keep in mind not to delete the default 2M chunk of tempdb which is a part of master device. In your case the segmap column should have the value 7(data+log). Also, take a dump of master db so as to recover in case something drastically goes wrong. If you've rectifed the problem using a different route, kindly let me know.
Cheers
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top