Large Log Ratio compared with data file size:

Large Log Ratio compared with data file size:

When log ratio is more than 2 times of data file size, how can we resolve to less size of log file than the data file as per database standard?

Run below query steps:

dbcc opentran

Output:

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator

backup log dbname with truncate_only

(If you run this above query, you are recommended to take full back up next after above query, if not it will break LSN sequence) otherwise you will get below error message during transaction log backup job operation.

System.Data.SqlClient.SqlError: BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.Smo)

if above query not working means, follow the below query

use master

Go
alter database dbname
set recovery simple

alter database dbname

set recovery full

use dbname

dbcc shrinkfile(dbname_log,sizetoshrink)

That’s it.

Hope you will resolve large log ratio issue