Search code examples
sql-serversql-server-2012database-backupsrecoverydatabase-administration

how to change log_reuse_wait and log_reuse_wait_desc

I have been created new database and the log file groth very past, and I get error mesaage that the log is full due to 'BACKUP'.

I looked in the differences between this Database and other databases in the SERVER, and I seen that in all databases log_reuse_wait is 0 and log_reuse_wait_desc is NOTHING and in my database log_reuse_wait is 0 and log_reuse_wait_desc is LOG_BACKUP.

I want to change this property in my database to 0 and NOTHING. How can I do that?

Solution

  • i found the solution. even the database is in SIMPLE mode is wait to BACKUP_LOG, so you need to change the recovery mode to FULL and then back to SIMPLE with no wait

    USE [master] 
GO  
ALTER DATABASE <db name> SET RECOVERY full  
GO
ALTER DATABASE <db name> SET RECOVERY SIMPLE WITH NO_WAIT;
GO
USE [db name]
GO
DBCC SHRINKFILE('<log file name>', 0, TRUNCATEONLY)