Monday, March 19, 2012

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

No comments:

Post a Comment