New feature to monitor transaction log growth rate (error with sum option)

[Former Member]

Hi  .

Trying to use

sp_logging_rate

Calculates the transaction log growth rate for the specified time period.

Syntax

sp_logging_rate {'full'|'sum', '[day,]hh:mm:ss'}[, interval='hh:mm:ss' | clear_option='y'|'n']

Full option working fine ...   but have issue with sum option ..

1> sp_logging_rate 'full', '00:03:00'   ------>  Working fine

2> go

But sum option giving below error  (to monitor transaction log growth rate for 24 hours  Sample every 3 minute ...

1> sp_logging_rate 'sum', '1,00:00:00','00:03:00'

2> go

Msg 17209, Level 16, State 1:

Server 'SYABSE_LAB', Procedure 'sp_logging_rate', Line 93:

Illegal format: '1,00:00:00  '.

Usage: sp_logging_rate {'full'|'sum', '[day,]hh:mm:ss'}[, @interval='hh:mm:ss'][, @clear_option='y'|'n']

(return status = 1)

Version :

Adaptive Server Enterprise/16.0 SP02 PL02/EBF 25318 SMP/P/RS6000/AIX 7.1/ase160sp02plx/2492/64-bit/FBO/Sun Nov 22 18:44:08 2015 

Thanks

Ajay Pandey     

[Former Member · Accepted solution]

Even result of output seems weird ... Growth rate in -ve also .

sp_logging_rate 'full','00:50:00'

go

Date Time                       Transaction Log Growth Rate GB/h

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

Aug  4 2016  9:13:40:136AM                18.514648

Aug  4 2016  9:13:50:233AM                30.011540

Aug  4 2016  9:14:00:340AM                31.211906

Aug  4 2016  9:14:10:436AM                57.722766

Aug  4 2016  9:14:20:653AM                38.193006

Aug  4 2016  9:14:30:833AM                26.891173

Aug  4 2016  9:17:14:466AM           -402175.536584

=========================

Total Summary Information

=========================

Transaction Log Growth Rate      Min GB/h        Max GB/h        Avg GB/h

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

                            -402175.536584      205.241671    -1316.801156

[former_member89972 · Accepted solution]

With the boatload of issues you are discovering , open a case with SAP/Sybase and file all your finds for the CR Bret opened earlier.

And in the meanwhile you can fix the issue(s) in your version to the extent possible.

HTH

Avinash

[former_member188958]

The problem is probably the comma in the first parameter. 
Instead of

sp_logging_rate 'sum', '1,00:00:00','00:03:00'

try

sp_logging_rate 'sum', '100:00:00','00:03:00'

[Former Member]

Still same problem ..

2> sp_logging_rate 'sum', '1,00:00:00','00:03:00'

3> go

Msg 17209, Level 16, State 1:

Server 'SYABSE_LAB', Procedure 'sp_logging_rate', Line 93:

Illegal format: '1,00:00:00  '.

Usage: sp_logging_rate {'full'|'sum', '[day,]hh:mm:ss'}[, @interval='hh:mm:ss'][, @clear_option='y'|'n']

(return status = 1)

1> sp_logging_rate 'sum', '100:00:00','00:03:00'

2> go

Msg 17209, Level 16, State 1:

Server 'SYABSE_LAB', Procedure 'sp_logging_rate', Line 93:

Illegal format: '100:00:00   '.

Usage: sp_logging_rate {'full'|'sum', '[day,]hh:mm:ss'}[, @interval='hh:mm:ss'][, @clear_option='y'|'n']

(return status

[former_member89972]

Looked at the code for sp_logging_rate.

Problem stems from string truncation in assignment.

Initial passed in variable @duration is char(12)

It is later assigned to variable @duration_time  which is char(8).

This is then used for calculation which results in error you reported.

You can fix this by recreating the code declaring the second variable @duration_time also as char(12).

HTH

Avinash

I verified by code snipped below

1> declare @duration char(12)

2> declare @duration_day int

3> declare @duration_time char(12)

4> declare @i int

5> declare @tmpstr char(20)

6>

7> select @duration = '1,00:00:00'

8> select @duration_day = 0

9> select @duration_time = rtrim(ltrim(@duration))

10>

11> select @duration

12> select @duration_time

13>

14> select @i = charindex(",", @duration_time)

15> if @i > 1

16> begin

17>     select @tmpstr = substring(@duration_time, 1, @i-1)

18>     begin

19>         select @duration_day = convert(int, @tmpstr)

20>         select @tmpstr = @duration_time

21>         select @duration_time = substring(@tmpstr, @i+1, 😎

22>     end

23> end

24> select @duration_time

25> go

(1 row affected)

(1 row affected)

(1 row affected)

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

1,00:00:00

(1 row affected)

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

1,00:00:00

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

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

00:00:00

(1 row affected)

1>

[Former Member]

Thanks Avinash,

Could you please create CRQ to fix  both  procedures in future release  ..

sp_logging_rate

sp_helpdefrag 

[former_member89972]

Ajay

Did you re-create the procedure sp_logging_rate  as suggested ?

I do not know about the issue for the second procedure you mention.

Is it something on similar lines ?

Folks from SAP/Sybase monitoring this forum will hopefully fix the procedure code next time around !

If my suggestion has solved your problem you can mark this answered.

Avinash

[Former Member]

This is prod server We are allowed to do that ..

sp_helpdefrag also have same kind of problem .  Kevin Sherlock  given work around  . We need it fixed by SAP ...

> sp_helpdefrag

2> go

Msg 247, Level 16, State 1:

Server 'XXXX', Procedure 'sp_helpdefrag', Line 182:

Arithmetic overflow during implicit conversion of FLOAT value '10.0' to a NUMERIC field .

[former_member89972]

Ajay

This is a bug in the system procedure as provided.

If you are a SAP/Sybase customer you should be able to open a case and request a fixed version.

My view is that this is NOT touching data of your company in any way.

It is fixing the logic for a system procedure in a system database.

So ....

Follow the change management process in your company

and fix the procedure if you do not want to wait

OR

Create your own proc and use it till the fixed version from SAP arrives.

The second procedure you mention is a completely different matter.

Avinash

[former_member188958]

I've opened CR 800812 for the sp_logging_rate issue and

CR 800813 for the sp_helpdefrag issue.

-bret

[Former Member]

Thanks Bret .

[Former Member]

I have created proc sp__logging_rate ... now this is working ...

thanks Avinash ,

[former_member89972]

Cool !!

And thanks to Bret for opening the CRs you wanted !

Cheers

Avinash

[Former Member]

I think . We still have problem ..

1> sp__logging_rate 'sum', '1,00:00:00','00:10:00'   --- Works

2> go

but

sp__logging_rate 'sum', '1,08:00:00','00:10:00'  --- Does not work ..

i changed in proc .. declare @duration_time char(12) /* Originally it was 8 */

[Former Member]

1> sp__logging_rate 'sum', '1,04:00:00','00:10:00'

2> go

Msg 17209, Level 16, State 1:

Server 'DS_CTO', Procedure 'sp__logging_rate', Line 92:

Illegal format: '1,04:00:00  '.

Usage: sp_logging_rate {'full'|'sum', '[day,]hh:mm:ss'}[, @interval='hh:mm:ss'][, @clear_option='y'|'n']

(return status = 1)

1> sp__logging_rate 'sum', '1,08:00:00','00:10:00'

2> go

Msg 17209, Level 16, State 1:

Server 'DS_CTO', Procedure 'sp__logging_rate', Line 92:

Illegal format: '1,08:00:00  '.

Usage: sp_logging_rate {'full'|'sum', '[day,]hh:mm:ss'}[, @interval='hh:mm:ss'][, @clear_option='y'|'n']

(return status = 1)

1>

[Former Member]

Even full option also have problem .. if included hh -- Clause does not work .

1> sp_logging_rate 'full','04:00:00'   --- does  not work

2> go

Msg 17209, Level 16, State 1:

Server 'XXXX', Procedure 'sp_logging_rate', Line 93:

Illegal format: '04:00:00    '.

Usage: sp_logging_rate {'full'|'sum', '[day,]hh:mm:ss'}[, @interval='hh:mm:ss'][, @clear_option='y'|'n']

(return status = 1)

1> quiy

2> quit

sp_logging_rate 'full','00:50:00'  --->>> This worl fine

go