Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Dec 9 '11 at 6:14

This question came from our site for professional and enthusiast programmers.

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 Dec 9 '11 at 6:10

2 Answers 2

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.

share|improve this answer
    
As a followup, please read the following blog posts: sqlskills.com/BLOGS/PAUL/category/Misconceptions.aspx –  Eric Humphrey - lotsahelp Dec 9 '11 at 14:31

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.

share|improve this answer
    
We do this on our testing databases on a daily basis in order to keep down the transaction logs. –  TOJE Dec 8 '11 at 23:27
2  
Changing the recovery model does NOT require restarting the SQL Instance. –  mrdenny Dec 9 '11 at 7:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.