Appropriate Time Out for Alter Database?

Platform: SQL Server 2000 or higher

Scenario: We have an application which is going to perform some changes to a database, and for those changes to be safe, we want to set the database to single_user mode. We'll be executing commands like:

ALTER DATABASE <DbName> SET READ_ONLY WITH ROLLBACK IMMEDIATE

ALTER DATABASE <DbName> SET MULTI_USER

etc.

Is 15 seconds a reasonable timeout for the alter commands? Does database size come into play when considering the time out for such commands? Is there a good rule of thumb for this sort of thing?

What exactly are you changing in the database, or are you talking changing tables?

Generally, alter database and alter table are "safe" to do on a running database. You do not need to set it into single user mode.

Yes, using alter table, size of the table being altered is a factor in how long it will take to complete.

Using alter database is almost instant unless you are creating a new huge file.

|||

Ultimately, it's a large number of operations, which include adding data, removing data, altering table definitions, etc. It's a process to change a schema, and the potential quantity of items is sufficient that we want to make sure that none of our users can make any changes while the process is running, which is why we want to set it into single user mode.

It sounds like giving the alter database command 15 seconds is generous enough, then. In my tests, I've seen it take up to 2 seconds, but the database I was testing on is a little bit smaller than our average database.

|||In that case, you should just use a transaction. That way it will be all or nothing and it will lock what it needs to do.

|||

Well, the scope of the project I'm doing doesn't actually allow me to explore modifying this particular aspect of the solution. One of my tasks is to determine if a 15 second time out is reasonable or not, and having no luck finding the answer to that question anywhere else, I came here to see if there were any guidelines I could follow, for making that determination.

The "just use transaction" answer implies that there are no conceivable situations where it'd be appropriate to set the database to single user mode. I think that there probably are situations where it'd be appropriate, though I can't site them offhand. I'd probably need to ask more experienced SQL experts to give me an example where it'd be appropriate. It may be that my assumption is wrong, and that I should just be using transactions, and I guarantee that the next project I have where I can make that choice, I will use transactions. For this project, I'm required to set the database in single user mode, and I suspect that the only part of the TSQL that I need to worry about taking a long time is the "ROLLBACK IMMEDIATE," because that's going to depend on the number of transactions at the time that need to be rolled back.

|||I don't understand your question about "time" to change to single user mode. I don't know how you intend to run your alter database commands. Assuming you are running a script, if you either add the single user mode command to the beginning or make a batch file which ran osql and the alter database to single user, then the run the rest of the update script, then the updates would not run until the single user mode command was done. Or just run the command manually and wait for it to finish.

Yes, the rollback is what will take the time, it might even fail. I have had times, when it is unable to rollback very large transactions, and it just hangs. Depending on your usage and situation at the time, it might be 2 seconds or it make never succeed.