All sorts of purposes: maintenance, rebuilding the database objects, dropping\/recreating the database in test environments, changing a state option like readonly (did you know that no users can be using the database when you make this change to RO)…. <\/P>
\n
Anyway, lots of reasons and this has always been the case. In previous releases we used to create sp_ (you know this is a special naming convention for objects in master that can be referenced in any database WITHOUT three-part naming<\/EM>) stored procedures that would detect the users using a database and either KILL spid<\/EM> directly or do a net send and then use WAITFOR DELAY to give the users a couple of seconds\/minutes\/whatever, to complete. Well, procedures like that are still useful (because you can set the database to “dbo use only,” warn the user, and then give them some time… However, warning them typically uses xp_cmdshell (which a lot of you don’t allow) and sometimes you don’t want to wait and\/or give them much time :) :).<\/P> So – did you know that ALTER DATABASE in SQL Server 2000 has been changed to allow termination options for STATE changes. The termination options allow one of three types of termination:<\/P> If no options are set and the process cannot get exclusive use of the database then it will till it can – indefinitely. Some database option changes generate a “could not get exclusive use” error but READ_ONLY (and a few others) don’t. If this is in an automated script\/process this can cause you a lot of grief. (In fact, I’m adding this note about 90 minutes after I created this blog entry…because my QA window is still trying to get exclusive access to pubs – which I was setting to readonly just to see if it would eventually time out….well, 90 minutes and still running it hasn’t. I think I’m going to kill it!<\/EM>)<\/P>
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n