SQL Server 2008 Recovery Mode reverts from FULL to SIMPLE

Question

Three of our SQL databases have their recovery model change every night from FULL to SIMPLE. The only jobs that I'm aware of are two BackupExec jobs that run nightly. Why would the recovery model change?

Backup Jobs: SQL FULL BACKUP, SQL LOG BACKUP

Event Manager: Event 5084: Setting Database option RECOVERY to SIMPLE for database databaseName

Does the event coincide with the time that Backup Exec is running jobs against your SQL Server? — Chris W, Jan 15 '10 at 17:23
Not that I'm aware of but that is the only job running at night that I'm aware of. — Eric Hazen, Jan 25 '10 at 22:37

score 0 · Accepted Answer · answered Jan 26 '10 at 00:20

I found a job running at 3am every night with the following:
enter code hereALTER DATABASE GBC_Reporting_3
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ('GBC_Reporting_3_log', 1);
DBCC SHRINKFILE ('GBC_Reporting_3_log' , 0, TRUNCATEONLY)
DBCC SHRINKFILE ('GBC_Reporting_3_2', 1);
DBCC SHRINKFILE ('GBC_Reporting_3_2' , 0, TRUNCATEONLY)
GO

We had to add the following to fix the problem.
-- Reset the database recovery model.
ALTER DATABASE GBC_Reporting_3
SET RECOVERY FULL;
GO

SQL Server 2008 Recovery Mode reverts from FULL to SIMPLE

1 Answers1