What Is Filling Up The Transaction Log

SELECT db.[name] AS [Database Name] ,
 db.recovery_model_desc AS [Recovery Model] ,
 db.log_reuse_wait_desc AS [Log Reuse Wait Description] ,
 ls.cntr_value AS [Log Size (KB)] ,
 lu.cntr_value AS [Log Used (KB)] ,
 CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)
 AS DECIMAL(18,2)) * 100 AS [Log Used %] ,
 db.[compatibility_level] AS [DB Compatibility Level] ,
 db.page_verify_option_desc AS [Page Verify Option]
FROM sys.databases AS db
 INNER JOIN sys.dm_os_performance_counters AS lu
 ON db.name = lu.instance_name
 INNER JOIN sys.dm_os_performance_counters AS ls
 ON db.name = ls.instance_name
WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
 AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;


Reference: http://www.sqlservercentral.com/scripts/DMV/71997/

T-SQL code to get the active Connections for each Database.

--Method 1

 SELECT 

    DB_NAME(dbid) as DBName, 

    COUNT(dbid) as NoOfConnections,

    loginame as LoginName

FROM

    sys.sysprocesses

WHERE 

    dbid > 0

GROUP BY 

    dbid, loginame

--Method 2

EXEC sp_who2

--Method 3

SELECT  spid,

        sp.[status],

        loginame [Login],

        hostname, 

        blocked BlkBy,

        sd.name DBName, 

        cmd Command,

        cpu CPUTime,

        physical_io DiskIO,

        last_batch LastBatch,

        [program_name] ProgramName   

FROM master.dbo.sysprocesses sp 

JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid

ORDER BY DBName,spid