how to add a datafile and log segments in sybase ASE.

[Former Member]

Dear Experts,

I am working on sybse ASE,

Please help in adding data file and log segments.I have searched but could not find any commands.

Regards,

Faheem.

[former_member187136 · Accepted solution]

Fareem,

Hope this answers your question:

1.Extend the Desired device. Start isql to connect to the database server and run the following SQL command:

use master

go

disk resize name = "<Device_Name>", size = "<Size>"

go

2.Add the new space on device <Device_Name> to database <DB_Name>

use master

go

alter database <DB_Name> on <Device_Name> = "<Size>"

go

or

2.Add the new space on log device <Device_Name> to database <DB_Name>

use master

go

alter database <DB_Name> log on <Device_Name> = "<Size>"

go

Regards

Kiran K Adharapuram

[Former Member · Accepted solution]

4> sp_helpdb CRP

5> go

name             db_size

         owner                dbid

         created

         durability

         lobcomplvl

         inrowlen

         status

---------------- ----------------------------------------------------

         -------------------- ----------------

         ------------------------------------------------

         ----------------------------------------

         ----------------------------------------

         --------------------------------

         ---------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------

-----------------------------------------

CRP                 81920.0 MB

         sapsa                   4

         Dec 17, 2014

         full

                100

             2000

         ddl in tran, allow nulls by default, abort tran on log full, enforce du

         mp tran sequence, allow wide dol rows, deferred table allocation, page

         compression, allow incremental dumps, deallocate first text page,full l

         ogging for all

(1 row affected)

device_fragments               size          usage

         created                   free kbytes

------------------------------ ------------- --------------------

         ------------------------- ----------------

CRP_data_001                      50176.0 MB data only

         Feb 24 2015  7:26PM                3280976

CRP_data_001                      11264.0 MB data only

         Feb 24 2015  7:26PM               10492528

CRP_log_001                       10240.0 MB log only

         Feb 24 2015  7:26PM       not applicable

CRP_data_001                      10240.0 MB data only

         Feb 24 2015  7:26PM               10444800

-----------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------

log only free kbytes = 1700032

(return status = 0)

[Former Member · Accepted solution]

this is the output.

[former_member187136 · Accepted solution]

1> alter database CRP log on 'CRP_log_001' = "1000M"

2>go

[Former Member · Accepted solution]

i dont want to increase the size, i want to add a new log segment.

[former_member187136 · Accepted solution]

Fahem,

Got it. Please try this

1>use master

2>go

1>disk init name='CRP_log_002',physname='/Path/CRP_log_002.dat',size='10000M'

2>go

1>alter database CRP log on CRP_log_002='10000M'

2>go

PS: For the parameter physname, specify the full path where your device should store(You can find that from the sp_helpdevice output)

Regards

Kiran K Adharapuram

[Former Member · Accepted solution]

Thanks, solved my problem...

[former_member187136 · Accepted solution]

Faheem,

Nice

Have a good day..!!!

Regards

Kiran K Adharapuram

[Former Member · Accepted solution]

Hi Kiran,

Thanks for your help, i am able to add log segments,

but it gave me some warning, can you please explain this to me?

Regards,

Faheem.

[former_member187136 · Accepted solution]

Faheem,

Thresholds are defined to take its action at already defined levels (ie, number of free page on a segment - log/device).  When you increase a segment's size (eg, extend the database log => increase the size of the logsegmentsegment), the previous threshold levels (number of free pages) are maintained.

The warning is simply stating that if any of your previous threshold levels are within 128 pages of the log's new last chance threshold (LCT; automatically redefined when the logsegment segment is increased) then said thresholds will (effectively) be marked as inactive.

If you have thresholds defined on your databases's log (ie, thresholds defined on logsegment segment) then you'll likely want to re-evaluate said thresholds to determine if you wish to modify said thresholds.  If you don't have any thresholds on your logsegment segment, or you do have thresholds but none of them are within 128 pages of the new LCT, then you can safely ignore this warning.

Regards

Kiran K Adharapuram