How to find space used by transaction logs ?

Hello all,
how can I find space in use in transaction log file of a database
at given moment of time ? ( programmatically)
( not using dbcc sqlperf(logspace)
thank you
jagdishHere is the code :

declare @.log_size float(53)
declare @.log_space_used float(53)
declare @.total decimal(20,4)

select @.log_size = cntr_value from master.dbo.sysperfinfo where instance_name = 'pubs' and counter_name ='Log File(s) Size (KB)'
select @.log_space_used = cntr_value from master.dbo.sysperfinfo where instance_name = 'pubs' and counter_name ='Log File(s) Used Size (KB)'

set @.total = (@.log_space_used/@.log_size) * 100

select 'Using ' + convert(varchar(20),@.total) + '% (' + convert(varchar(20),@.log_space_used) + ' MB) of ' + convert(varchar(20),@.log_size) + ' MB' as 'Report'

.................
note : this is script against pubs database ,so change the name !

Cheers .
srdjan|||Thank you for your help

How to find how Activation has failed

I have seen the posting on determining how activation has failed and looking through the system logs is very helpful in determining why activation is not occuring, however, short of looking through the SQL Server logs is there another way to get the same information? Access to the SQL Server logs is fairly restricted. Does anyone know another way that this can be done? I have used the execute as technique described in the article on "Troubleshooting Activation Stored Procedures" and found it helpful in some cases.

Gary

There is a profiler trace event for "Broker:Activation" which should report an event when an activated task is aborted. However, if the activation stored procedure produces an output due to an error, this only gets logged to ERRORLOG and event log. Unfortunately, SQL Server 2005 does not have a better error reporting mechanism. However, you can wrap your stored procedure in TRY/CATCH block and log exceptions to a table. That will at least catch exceptions in the stored proc itself.

Hope that helps,
Rushi

|||Thanks Rushi,

I guess I can work with that. The problem is that priveledges to see the ERRORLOG and Event logs aren't typically given to developers. I know that now in 2005 you can profile without being SA which is a big help.

Thank you for your prompt reply,

Gary

|||

We certainly need better logging mechanism for activated tasks than writing to ERRORLOG. If you have suggestions please write to product feedback, which will help in planning future releases.

Thanks,
Rushi

|||Thanks Rushi,
I added feedback for this issue.

Gary