November 1, 2006 at 4:11 pm
Hi All,
Normally I would detach the database through Enterprise manager and check if there are any connections to the database. But, now for snapmirror updating I need to take the database offline and destroy the volumes to get the latest data/log files.
so, for this, I need to detach the db through a script and am wondering what will happen when you run sp_detach_db 'dbname', 'true' and some processes are still running? Will it try to rollback what it has done before it actually detaches the database?
Thanks heaps in advance.
TK
November 1, 2006 at 5:24 pm
You'll get a 'database in use' error and the detach will fail. You must have exclusive access to the database to detach it.
Greg
Greg
November 1, 2006 at 5:29 pm
How can I ensure that I have exclusive access to the database? I know that we will have to kill the active processes, but how to be sure that the active processes have finished rollbacking now, and that now I have exclusive access?
November 2, 2006 at 2:38 am
Run the command EXEC sp_who against your db in Query Analyser. That will give you the SPID connections to your db. Check this link for processes you shouldn't kill: http://msdn2.microsoft.com/en-US/library/ms173730.aspx After you've issued the KILL command , run KILL WITH STATUSONLY to check on rollback progress.
Good luck.
November 2, 2006 at 8:43 am
To prevent other connections once you've killed existing ones, you could restrict access to the database going to the Options tab of database properties in Enterprise Manager. Check the "Restrict Access" box and choose "Members of db_owner, db_creator, or sysadmin" or "Single_user".
Greg
Greg
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply