rigth way to find free space on log segment

[Former Member]

-- Server information

-- Adaptive Server Enterprise/15.0.3/EBF 17690 ESD#1.1 RELSE/P/Solaris AMD64/OS 5.10/ase1503/2681/64-bit/FBO/Thu Aug 20 15:37:40 2009

Good afternoon,

I tried the following command to find out the free space in the log segment

1.dbcc checktable(syslogs)

2.sp_helpsegment logsegment

3.select data_pages(db_id('DB_NAME'),8,0)

4.sp_spaceused syslogs

solution 1/2/4 give about the same result but solution 1 might be long to compute when the log segment is full.

How do you efficiently compute free space in log segment?

Thanks all

Simon

[Former Member]

Hi Mark,

Indeed I didn't think of setting multiple thresholds for monitoring purpose.

My need is that I need to upgrade some databases. To do this we have a tool that will launch plenty of steps and I wanted to monitor the log segment during the execution to know which step is filling the syslogs.

If setting several thresholds doesn't add too many overhead, I'll give a try.

Thanks Mark

[corral · Accepted solution]

In ASE 12.5 we used

select lct_admin('logsegment_freepages',  9 )

where 9 is the dbid of the database; that is, db_id('DATABASE_NAME') Result is expressed in pages.

I think we extracted that query from the text of sp_helpdb. You could do the same, read the sp_helpdb SQL definition run by installmaster and see how it manages to get the logsegment free space figure.

Regards,

Mariano Corral

[Former Member · Accepted solution]

Thank you Mariano,

Indeed, this is what we did in the mean thime (reading sp_helpdb code)

Regards,

Simon

[SybDBA · Accepted solution]

Kudos... Sir !!

Regards

--

Pankaj