Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Table full help

Posted on 2009-07-09
15
485 Views
Last Modified: 2012-05-07
Hi,

I have Adaptive Server Enterprise/12.5.2/EBF 11798/P/NT

I received this message when trying to load data

Server 'MOBILE7_PR', Line 1:

Can't allocate space for object 'obj_prof' in database 'SARK' because 'default'
segment is full/has no free extents. If you ran out of space in syslogs, dump
the transaction log. Otherwise, use ALTER DATABASE to increase the size of the
segment.


When I login as SA then followed by use SARK, I ran sp_helpdb


1> sp_helpdb
2> go
 name                     db_size       owner                    dbid
         created
         status
 ------------------------ ------------- ------------------------ ------
         --------------
         ------------------------------------------------------------------------------------------------------
 SARK                          1660.0 MB sarkadmin                      4
         Oct 28, 2008
         select into/bulkcopy/pllsort, trunc log on chkpt
 master                         24.0 MB sa                            1
         Oct 28, 2008
         mixed log and data
 model                           8.0 MB sa                            3
         Oct 28, 2008
         mixed log and data
 sybsystemdb                     8.0 MB sa                        31513
         Oct 28, 2008
         mixed log and data
 sybsystemprocs                120.0 MB sa                        31514
         Oct 28, 2008
         trunc log on chkpt, mixed log and data
 tempdb                        258.0 MB sa                            2
         Jul 01, 2009
         select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data

(1 row affected)
(return status = 0)

I need help to increase something so I can have enough space.  I went to Sybase Central and tried to edit this, but it wont allow me to increase the size.

How can I add more space in order to bypass my error with not enough space.

Thanks
0
Comment
Question by:richsark
  • 8
  • 7
15 Comments
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 24815150
Easy.  I will stay up until you close this ticket.

That's database full, btw.

1 Find out which devices you have space available in
- in SybaseCentral: in the explorer view: look for devices and evaluate them for unused (free) space
- also look at them and find out exactly which devices SARK is located on
- Devices belong to the Server; dbs are allocated on them; first we will try to allocate space on devices where SARK exists
--- then on devices where SARK does not exist
--- then create a new device if we have to

2  Find out wher SARK is and what the exact space situation is
- In SYbase Central, tools menu I think, look for "Interactive SQL" and run it
- in Interactive SQL: choose SARK in the db_name inwdow (top right IIRC)
- in the command window (main, top half) enter:
sp_helpdb SARK
<execute>  -- F5 key

Report back devices that have unused space; SARK space details.

Cheers

0
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 24815170
3  In Interactive SQL, you can also:
sp_helpdevice
<execute>  -- F5 key

You need to get your head around this, so do ask questions.

Cheers

0
 
LVL 1

Author Comment

by:richsark
ID: 24815476
Hi, I dont seem to have interactive sql.

But Under Sybase central under database Devices

I have master at 125.00 MB and unused 77.00
sark_dat  1100.00 MB and unused is 0.00
sark_log 560.00 MB and 0.00 unused
sark_tempdb 350 MB and 100.00MB unused
sysprocdev 120.00 MB and 0.00 unused

I think I need to add at 500 more MB somewhere in order to have enough space to load obj_prof




1> sp_helpdb SARK
2> go
 name                     db_size       owner                    dbid
         created
         status
 ------------------------ ------------- ------------------------ ------
         --------------
         ------------------------------------------------------------------------------------------------------
 SARK                          1660.0 MB sarkadmin                      4
         Oct 28, 2008
         select into/bulkcopy/pllsort, trunc log on chkpt

(1 row affected)
 device_fragments               size          usage
         created             free kbytes
 ------------------------------ ------------- --------------------
         ------------------- ----------------
 sark_dat                             600.0 MB data only
         Oct 28 2008  9:22AM            15736
 sark_log                             310.0 MB log only
         Oct 28 2008  9:22AM not applicable
 sark_dat                             500.0 MB data only
         Nov  3 2008  3:57PM            55704
 sark_log                             250.0 MB log only
         Nov  3 2008  4:07PM not applicable

 --------------------------------------------------------------
 log only free kbytes = 571192
 device
         segment



 ------------------------------
         -----------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
 sark_dat
         default



 sark_dat
         system



 sark_log
         logsegment



(return status = 0)
1>
 
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 6

Expert Comment

by:IncisiveOne
ID: 24815736
Ok.

Choose one:
a.  SybaseCentral-> Tools -> AdaptiveServerEnterprise -> Interactive SQL (I will give you exact commands)
b. SybaseCentral (I will give you instructions; you have to navigate the GUI)

500 mb on top of 1100 mb is an awful lot, 45% new added space, but suit yourself.
In that case, you need to create a new device.  Do you have 500mb free disk space ?

Assuming you do, to reduce the back-and-forth, do [3] above but:
a = sp_helpdevice sark_dat
b = choose Devices-> sark_dat; then File-> Generate DDL
-- that will give you exact details re where you current devices are, and hint at where you are going to find free disk space
-- post back

You have some anomalies in the resource allocation, but we will leave that for later

Cheers

0
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 24815870
Rich

It is 0315 here; I believe it is 1315 there.  I am waiting for your response.
0
 
LVL 1

Author Comment

by:richsark
ID: 24816362
HI, I am here, I just got out of a meeting. Give me a few min to review your note
0
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 24816490
Ok, so it is your NT box, not your Solaris production system, that is full.

0
 
LVL 1

Author Comment

by:richsark
ID: 24816519
Ok,

1> sp_helpdevice sark_dat
2> go
 device_name                    physical_name
         description



         status cntrltype device_number
         low
         high
 ------------------------------ ----------------------------------------------
         -----------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
         ------ --------- -------------
         --------------------------------------------------------------------------------
         --------------------------------------------------------------------------------
 sark_dat                        e:\sybase-62\data\sark_dat
         special, dsync on, physical disk, 1100.00 MB



          16386         0             2
                                                                                 33554432
                                                                                 34117631

(1 row affected)
(return status = 0)


I also do not see:

a.  SybaseCentral-> Tools -> AdaptiveServerEnterprise -> Interactive SQL (I will give you exact commands)
b. SybaseCentral (I will give you instructions; you have to navigate the GUI)

All I have is Sybase Central Java edition

If I goto tools, The only options I have are

Connect
Disconnect
Connection_Profiles
Plugins
Options


I have 1.6 gigs free, so I can do another 500MB to SARK for sark_dat which I think has 1660.0 MB defined and not enough


I am not following your key stroks, please re-define

Assuming you do, to reduce the back-and-forth, do [3] above but:
a = sp_helpdevice sark_dat
b = choose Devices-> sark_dat; then File-> Generate DDL
-- that will give you exact details re where you current devices are, and hint at where you are going to find free disk space


Thanks
0
 
LVL 1

Author Comment

by:richsark
ID: 24816547
Yes my NT box
0
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 24816931
keystrokes.  Don't worry.

You are probably on a 5-year-out-of-date version of SybaseCentral, as is your ASE.  Therefore the menus, and options are quite different.  Interactive SQL was not written then.  Let's stick to isql or whatever you are using thus far.

disk init  name  = 'sark_dat_2',
physname  = 'e:\sybase-62\data\sark_dat_2', -- wherever you have space
size  = '500M', dsync = true
-- , vdevno =xx
go

Warning.  You might have to give it a vdevno (older versions required it; I am giving you code for 15.0.2/12.5.4).  Count the no of devices in SC, and add 1. De-comment the commented line above, and replace xx with that number

Cheers

0
 
LVL 1

Author Comment

by:richsark
ID: 24817030
Ok, I am good until we get to -- , vdevno =xx
the xx should be what?

Do I  log in as SA or SARKADMIN

how will sybase know to use that extra 500MB to sark_dat

The key strokes I have ( please correct)

isql -U sarkadmin -P sarkadmin

1>disk init  name  = 'sark_dat_2',
2>physname  = 'e:\sybase-62\data\sark_dat_2',
3>size  = '500M', dsync = true

4>go

Thanks
0
 
LVL 6

Accepted Solution

by:
IncisiveOne earned 500 total points
ID: 24817276
From "the keystrokes I have" ...

did those keystrokes work, no error messages ?  If so, forget about vdevno, and we'll get on with the next step

If not:  Count the no of devices in SybaseCentral, and add 1.replace xx below with that number

isql -U sarkadmin -P sarkadmin  <-- needs "sa_role" which sarkadmin probably has

1>disk init  name  = 'sark_dat_2',
2>physname  = 'e:\sybase-62\data\sark_dat_2',
3>size  = '500M', dsync = true
4>, vdevno = xx
5>go

0
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 24817299
If it works as sarkadmin, fine, you have "sa_role"; if not, log in as "sa"

note the comma in line 4>
0
 
LVL 1

Author Comment

by:richsark
ID: 24822241
Hi,

Done, I ran it without the vdevno = xx

Whats next?
0
 
LVL 1

Author Comment

by:richsark
ID: 24823246
Hi,

I got it,

The complete key strokes

 disk init name = 'sark_dat_2',
 physname = 'e:\sybase-62\data\sark_dat_2',
 size  = '300M', dsync = true

vdevno=5 ( next avaliable number in Sybase Central)

alter database QIP
on sark_dat_2=300 ( 300 is the size you just created above)

And my data loaded fine

Thanks for the tips !
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Taking long time 7 481
Sybase initialize new disk in Solaris 10 failed 2 950
Sybase - Backup and restore db into other server 1 575
sybase T-sql different 2 116
This article explains the steps required to use the default Photos screensaver to display branding/corporate images
Learn how to PXE Boot both BIOS & UEFI machines with DHCP Policies and Custom Vendor Classes
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question