IF OBJECT_ID ('dbo.sp_xLogSpaceUsed') IS NOT NULL
	DROP PROCEDURE dbo.sp_xLogSpaceUsed
GO

create proc sp_xLogSpaceUsed (@show_drives int = 0)
as
begin
	set nocount on
	
	CREATE TABLE #sqlperf
		(
		Database_Name NVARCHAR (128),
		Log_SizeMB REAL,
		Log_SpaceUsedPct REAL,
		Status INT,
		MaxSizeMB REAL null,
		Drive varchar(3),
		DiskFreeMB REAL null
		)
	
	CREATE TABLE #availablemedia
		(
		name NVARCHAR (255),
		low_free INT,
		high_free INT,
		media_type TINYINT,
		freemb real null
		)
	
	insert into #sqlperf (Database_Name,Log_SizeMB,Log_SpaceUsedPct,Status)
	exec ('dbcc sqlperf (logspace)')
	
	insert into #availablemedia (name,low_free,high_free,media_type)
	exec xp_availablemedia
	
	update #availablemedia
	set freemb = ((power(convert(bigint,2),32) * high_free ) + convert(bigint,convert(binary(4),low_free)))/(1024*1024.)
	
	select	f.database_id
		,	a.name drive_name
		,	f.is_percent_growth
		,	f.growth
		,	sum(f.size)*(8/1024.) sizemb
		,	case when min(max_size)=-1 then -1 else sum(max_size)*(8/1024.) end max_sizemb
	into	#master_files
	from	sys.master_files f 
	join	#availablemedia	a on a.name = substring(f.physical_name, 1, len(a.name))
	where f.type_desc = 'LOG'
	group by f.database_id, a.name, f.is_percent_growth, f.growth
	
	update	#sqlperf
	set		MaxSizeMB = 
		case when f.is_percent_growth=1
		then 
			convert(numeric(38,4),(f.sizemb)) *
			power( (1 + (f.growth/100.0) ),
				 	convert(int,log (
						case 
						when f.max_sizemb = -1 then a.freemb + (f.sizemb)	-- Current Size + Disk Available.
						else 
							case when (a.freemb + (f.sizemb)) > (f.max_sizemb)
							then f.max_sizemb
							else (a.freemb + (f.sizemb))
							end
						end
						/ (f.sizemb)
					)
					/ log (1 + (f.growth/100.0) )
					) 
				)
		else 
			(f.sizemb)
			+ ((8*f.growth)/1024.) * 
				(
				   (case 
					when f.max_sizemb = -1 then a.freemb + (f.sizemb)	-- Current Size + Disk Available.
					else 
						case when (a.freemb + (f.sizemb)) > (f.max_sizemb)
						then f.max_sizemb
						else (a.freemb + (f.sizemb))
						end
					end
					)	- (
					(f.sizemb)
					) 
				) / (
					f.growth*(8/1024.)
				)
		end
		,	#sqlperf.DiskFreeMB = a.freemb
		,	#sqlperf.Drive		= a.name
	from	#sqlperf
	join	#master_files 	f on db_id(#sqlperf.Database_Name) = f.database_id
	join	#availablemedia a on a.name = f.drive_name
	
	set nocount off
	
	if @show_drives = 1 
		select	Database_Name		
			,	Drive
			,	convert(numeric(20,1), DiskFreeMB) 			DiskFreeMB
			,	convert(numeric(20,1), Log_SizeMB)			LogAllocatedMB
			,	convert(numeric(20,1), ((Log_SizeMB*(Log_SpaceUsedPct/100.)))) LogSpaceUsedMB
			,	convert(numeric(20,1), Log_SpaceUsedPct)	AllocatedUsedPct
			,	convert(numeric(20,1), MaxSizeMB)			EffectiveMaxSizeMB
			,	convert(numeric(20,1), 100*((Log_SizeMB*(Log_SpaceUsedPct/100.)) / MaxSizeMB)) EffectiveUsedPct
		from #sqlperf
	else
		select	Database_Name		
			,	convert(numeric(20,1), Log_SizeMB)			LogAllocatedMB
			,	convert(numeric(20,1), ((Log_SizeMB*(Log_SpaceUsedPct/100.)))) LogSpaceUsedMB
			,	convert(numeric(20,1), Log_SpaceUsedPct)	AllocatedUsedPct
			,	convert(numeric(20,1), MaxSizeMB)			EffectiveMaxSizeMB
			,	convert(numeric(20,1), 100*((Log_SizeMB*(Log_SpaceUsedPct/100.)) / MaxSizeMB)) EffectiveUsedPct
		from #sqlperf
	
	drop table #master_files
	drop table #availablemedia
	drop table #sqlperf
end





GO

GRANT EXECUTE ON dbo.sp_xLogSpaceUsed TO public
GO

exec sp_xLogSpaceUsed 1