Solved

Table full help

Posted on 2009-07-09
15
464 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article, I will show you HOW TO: Suppress Configuration Issues and Warnings Alert displayed in Summary status for ESXi 6.5 after enabling SSH or ESXi Shell.
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now