I have a database with datafile 40GB and Logfile 47GB and it is in full recovery mode and transactional logs are backed up every 2 hrs and auto truncates. What steps should I follow so that the database does not have huge transactional logfile since it will be used for data warehouse. Transactional logfile Autogrowth is set to 256 MB and Mx fie Size 2 TB.
SELECT name, log_reuse_wait_desc FROM sys.databases order by name; log_reuse_wait_desc = Log_Backup
DBCC SQLPERF(logspace) 47879.99 MB Log Size and Log Space Used 2.172195 %
SELECT DB_NAME(database_id) AS DatabaseName ,
database_id ,
CAST(( total_log_size_in_bytes / 1048576.0 ) AS DECIMAL(10, 1))
AS TotalLogSizeMB ,
CAST(( used_log_space_in_bytes / 1048576.0 ) AS DECIMAL(10, 1))
AS LogSpaceUsedMB ,
CAST(used_log_space_in_percent AS DECIMAL(10, 1)) AS LogSpaceUsedPercent
FROM sys.dm_db_log_space_usage;
DatabaseName database_id TotalLogSizeMB LogSpaceUsedMB LogSpaceUsedPercent
DB 43 47880.0 1040.0 2.2