Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Table full help

Posted on 2009-07-09
15
Medium Priority
?
505 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
[X]
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
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

722 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