During my daily maintenance backup task, I am attempting to set specific databases to recovery model simple along with shrinking the database.
ALTER DATABASE db_name SET RECOVERY SIMPLE
GO
DBCC SHRINKDATABASE (db_name)
GO
This happens at 1am along with some other tasks that use the same database. However, I am getting an error where
Execute T-SQL Statement Task (AERIS\SQL2008) Execute TSQL on Local server connection Execution time out: 0 Task start: 2011-12-08T01:00:04.
Task end: 2011-12-08T01:16:45.
Failed:(-1073548784) Executing the query
"alter database db_name set recovery simple" failed with the following error: "ALTER DATABASE failed because a lock could not be
placed on database 'db_name'. Try again later.
ALTER DATABASE statement failed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.
Could the issue be that since a user is logged on the the server at the point the recovery model is being changed? I'm puzzled since sometimes the recovery model change works intermittently.
Best Answer
If you change the recovery model to SIMPLE to back to either FULL or BULK_RECOVERY you make all your transaction log backups useless. The recovery model shouldn't every be changed regularly.
Shrinking the database is simply fragmenting all the objects within the database tables.
You should stop doing both of these things.