Large Log Ratio compared with data file size:
alter database dbname
set recovery simple
Hope you will resolve large log ratio issue
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
Goalter database dbname
set recovery simple
alter database dbname
set recovery fulluse dbname
dbcc shrinkfile(dbname_log,sizetoshrink)That’s it.
No comments:
Post a Comment