DBCC SQLPERF(logspace)

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

DECLARE @sql_command varchar(1024)
DECLARE @logtable TABLE
(
   [Database Name] VARCHAR(1000) NULL,  
   [Log Size (MB)] numeric,
   [Log Space Used (%)] numeric,
   [Status] VARCHAR(128) NULL  
)

SELECT @sql_command = 'dbcc sqlperf (logspace)'

INSERT INTO @logtable EXEC (@sql_command)

select * from @logtable where [Database Name] = 'RFDB'

SELECT /* ignore this */ command,
            r.session_id, r.blocking_session_id,
            s.text,
            start_time,
            percent_complete, 
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

DECLARE @whotable TABLE
(
   SPID INT,  
   Status VARCHAR(1000) NULL,  
   Login SYSNAME NULL,  
   HostName SYSNAME NULL,  
   BlkBy SYSNAME NULL,  
   DBName SYSNAME NULL,  
   Command VARCHAR(1000) NULL,  
   CPUTime INT NULL,  
   DiskIO INT NULL,  
   LastBatch VARCHAR(1000) NULL,  
   ProgramName VARCHAR(1000) NULL,  
   SPID2 INT,
   REQUESTID INT
)
 
INSERT INTO @whotable EXEC sp_who2
 
select * from @whotable order by BlkBy desc