Shrink Log file +sybase ASE

[Former Member]

Hi All,

I ma new to sybase ASE ,I ma unbale to take the transaction log backup through sp_dumptrans command its shwoing message "Stored procedure 'SP_dumptrans' not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output)."

After that I am having the space in log file physical directory I have run alter database comand now MY physcal drive is 100 GB after latering databse its reached 95 GB.

How can I shrink the log file and to decrease the log file size .

Please suggest

[Former Member]

Use the following command to dump the transaction log

login to the isql utility and do the below

(example code)

use <dbname>

go

dump tran <dbname> to '/sybase/backup/tran.dmp'

go

Once you have executed the above command all the data

in the transaction file will be removed and the space

will be reused by sybase.

If you want to shrink the log file then execute the below code

Remove the last 1GB from transaction log

(example code)

use <dbname>

alter database <dbname> log off dev2=1024 ----------------in MB

go

regards,

Suresh Daniel

[ivan_martis3]

Hi,

Please follow SAP Note 1588316 - SYB: Configure automatic database and log backups

to create a config.

For all ASE versions prior to 15.7.0.021, see the attached old version of this note.)

Regards

Ivan

[Former Member]

Hi suresh,

As per your sugegstion I have run the comand but its storing in C directory .

use <dbname>

go

dump tran <dbname> to '/sybase/backup/tran.dmp'

go

1> dump tran QAS to '/sybase/backup/tran.dmp'

2> go

Backup Server session id is: 60. Use this value when executing the

'sp_volchanged' system stored procedure after fulfilling any volume change

request from the Backup Server.

Backup Server: 4.41.1.1: Creating new disk file

C:\Windows\system32/sybase/backup/tran.dmp.

Backup Server: 6.28.1.1: Dumpfile name 'QAS133460EAF1    ' section number 1

mounted on disk file 'C:\Windows\system32/sybase/backup/tran.dmp'

Backup Server: 4.58.1.1: Database QAS: 298772 kilobytes DUMPED.

Backup Server: 3.43.1.1: Dump phase number 3 completed.

Backup Server: 4.58.1.1: Database QAS: 298794 kilobytes DUMPED.

Backup Server: 3.42.1.1: DUMP is complete (database QAS).

1> exit

But I ma trying to run the comand sp_dumptrans I am getting below error and log file is not reduced .

E:\backups>isql -SQAS -Usapsa -PWelcome12345 -i sp_dumptrans.sql
Msg 1105, Level 17, State 2:
Server 'QAS', Procedure 'sp_dumptrans':
Can't allocate space for object 'sysprocedures' in database 'saptools' because
'system' 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.
Msg 2811, Level 16, State 2:
Server 'QAS', Procedure 'sp_dumptrans':
Cannot create procedure dbid 5, objid 780578838, with a group number of 1.

Please sugegst

[former_member188958]

On the first issue, on windows you can specify the drive as part of the dump file name:

dump tran <dbname> to 'F:/sybase/backup/tran.dmp'

On the second issue:  If saptools is a mixed data and log database, it may be that all you need to do is dump tran on the saptools database.  If saptools has seperated segments, then you need to enlarge the saptools database at least slightly to make more room for system data.  One more MB is likely enough.

[Former Member]

Hi Bret,

same has been dumpted tran.dmp,but I am trying to dump through sp_dumptrans I am unable take the transactiona l backup ,I am getting the message"

E:\backups>isql -SQAS -Usapsa -PWelcome12345 -i sp_dumptrans.sql
Msg 1105, Level 17, State 2:
Server 'QAS', Procedure 'sp_dumptrans':
Can't allocate space for object 'sysprocedures' in database 'saptools' because
'system' 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.
Msg 2811, Level 16, State 2:
Server 'QAS', Procedure 'sp_dumptrans':
Cannot create procedure dbid 5, objid 780578838, with a group number of 1.

Please sugegst how can we take the backup .

Please suggest

[Former Member]

Hello all,

Any one please sugegst on above

[Sriram2009]

Hi

Kindly refer the SAP link for shrink

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.help.ase.15.7.sp50/doc/html...

Regards

SS