2

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.

Improve this question
1
  • 1
    The error just means that the query couldn't put an exclusive lock on the database to change the recovery model, so that means someone or something else was still connected to that database
    – marc_s
    Commented Dec 9, 2011 at 6:10

2 Answers 2

Reset to default
3

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.

Improve this answer
1
-3

You have to restart the database server after a change of recovery model , so what you are doing isn't going to work anyway.

Why are you shrinking on a daily basis anyway, it's at best a complete waste of time in nearly all cases.

Improve this answer
2
  • We do this on our testing databases on a daily basis in order to keep down the transaction logs.
    – TOJE
    Commented Dec 8, 2011 at 23:27
  • 3
    Changing the recovery model does NOT require restarting the SQL Instance.
    – mrdenny
    Commented Dec 9, 2011 at 7:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.