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 

Comments

Popular posts from this blog

SSIS2008R2 Error: 0xC0209303 at Package:

SQL2008R2 Error: The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)