Thursday, September 22, 2011

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 

No comments:

Post a Comment

MYSQL::Setting Validate_Password componet for MySQL Database to ensure password policy settings

Inadequate Password Settings for MySQL Database We observed that the `validate_password%` settings on hostname `<insert hostname>` a...