cancel
Showing results for 
Search instead for 
Did you mean: 

transacion log dump size

Former Member
0 Kudos

Hello,

Every time that I dump the transaction log's database is created a file of 1-2gb size.

The job is scheduled several times on a day, but every time It has the same size.

Any suggestion about this behavior?

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello,

DB version is 15.7.0.103.

I've executed the dump transaction log and dump file of 3GB is created, few minute after I´ve launched again the dump transaction and the file size is similar to previous file created...

former_member188958
Active Contributor
0 Kudos

When you issue a dump transaction, ASE writes out the entire transaction log to the archive file and truncates the log from the beginning of the log to the start of the oldest open transaction.

If the oldest open transaction stays the same over several dumps, the same content (plus perhaps some new content) will be written out to the archive each time.

You can use the master..syslogshold table to check which open transaction is the oldest in each database. 

Former Member
0 Kudos

Hello Bret, How can I check the open transactions into master and sysloghold? This night the backup job has executed 3 times when It's scheduled only one. The first one has finished correctly but I can see two executions more but they weren't finished correctly for avalaible space. Regards

former_member207908
Participant
0 Kudos

Hello Rubens,

To check the oldest open transaction using isql utility, use the following sql query,

select dbid "Database ID", spid SPID, starttime "START TIME", name NAME from master..syslogshold where spid <> 0

Let us know the frequency of your database dump and transaction dump.

Also, if you can share the screen shot of the dump files location, and logs/errors, it would be easy for us to understand

Regards

Rajesh

Former Member
0 Kudos

Hello Rajesh, The output of sentence is

PRD    SPID  START TIME         NAME ------ ------ -------------------------------         ------------------------------------------------------------   

4    205            Dec 23 2014 10:05AM         $chained_transaction

Database backup is made every day 9:00PM and every 6 hours the transaction dump.

former_member207908
Participant
0 Kudos

Adding to Mark and Reddy, I would suggest you to change the frequency of the transaction log dumps to every 1 hour and the obvious reason for transaction dump size is its frequency.


Answers (3)

Answers (3)

victoria_normand
Contributor
0 Kudos

Hi Ruben,

Following Bret's comment it would be interesting to know if you are using SAP applications on ASE ? (Business Suite / Business Warehouse?), I'm asking because I have seen some questions non-SAP app related posted here

Regards,
Victoria.

former_member188958
Active Contributor
0 Kudos

I suggest that it is important to include the version of ASE you are using when it might be relevant.

Are you dumping to the same file name, and are the files always exactly the same size?  If so, then I suspect you are using a very old version of ASE, earlier than 15.0.2.  Prior to 15.0.2, if you dumped to a file that already existed, backupserver overwrote that file and if the previous file had been larger, it retained its previous size.  In 15.0.2, backupserver deletes the old file and creates a new file.

-bret

former_member187136
Contributor
0 Kudos

Good Point Bret

former_member187136
Contributor
0 Kudos

Ruben,

The size of the transaction log is determined by:

     The amount of update activity in the associated database

     The frequency of transaction log dumps

In databases that have a lot of insert and update activity, logs can grow very quickly. To determine the required log size, periodically check the size of the log. This will also help you choose thresholds for the log and scheduling the timing of transaction log dumps.

However, there is a command to determine the size of tran log file:

dbcc checktable(syslogs):

dbcc reports the number of data pages being used by the log. If your log is on a separate device, dbcc checktable also tells you how much space is used and how much is free.

or use the below statement to verify:

select count(*) from syslogs

Regards

Kiran Kumar A