Cannot Detach The Database Error 3703
Contents |
Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the cannot detach a suspect or recovery pending workings and policies of this site About Us Learn more about Stack cannot drop database because it is currently in use sql server Overflow the company Business Learn more about hiring developers or posting ads with us Server Fault Questions Tags sql server detach database script Users Badges Unanswered Ask Question _ Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute: Sign up Here's how
Sp_detach_db
it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top How can I detach a database that is in use? up vote 6 down vote favorite 4 Even though nothing is using it I am unable to detach a database because it is use. Cannot detach the database 'DEMO' detach database sql server because it is currently in use. I have tried rebooting and get the same message. sql-server-2005 share|improve this question asked Oct 20 '09 at 17:29 Jack B Nimble 1,3201912 add a comment| 4 Answers 4 active oldest votes up vote 12 down vote accepted --Kick all users off of the database NOW ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE --Kick all but after 60 seconds ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS --restore connection to users ALTER DATABASE YourDatabase SET MULTI_USER share|improve this answer answered Oct 20 '09 at 18:05 RateControl 1,117819 add a comment| up vote 6 down vote Have you tried checking the "Drop Connections" box when you detach it? share|improve this answer answered Oct 20 '09 at 17:33 joeqwerty 83.4k246121 I was hoping more for a query related solution. Server Management Studio Express very often causes my machine to blue screen. So I'm using a tool that just sends queries to the server. –Jack B Nimble Oct 20 '09 at 17:54 A blue screen is usually rela
Cannot detach the database 'Database Name' because it is currently in use. (Microsoft SQL Server, Error: 3703). This almost always happens due to an open connection which you can drop during the detachment process, but I early type of aircraft like to exclude that option. This helps determine if the database still has connections to
Heather
anything. There are two queries you can run that will tell you what is connected to the database. First run the below query. select * from sys.sysprocesses where dbid = db_id('Database Name') The returned results will display a host name, and a login name. Using the two you can determine if the connection is initiated locally, or externally. If http://serverfault.com/questions/76432/how-can-i-detach-a-database-that-is-in-use externally, simply go to the server that is connecting to it and remove any User or System DSNs. If none are listed the database is likely configured inside of an application. Refer to your documentation for whatever application resides on the server in order to remove the database connections. If you find that the connection was initiated locally, you will need to run the below query. This query provides information about current users, sessions, and http://www.vtechie.com/2014/02/detatch-database-failed-for-server-sql.html processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session. sp_who Using the status, loginname, hostname, dbname, and cmd fields you can likely determine what process is locking the database locally. In almost all cases I've seen it is due to a backup, transaction log shipping, or some other type of SQL related task. If you've managed to track down the culprit and removed the connection to the database you should now be able to proceed with detaching the database without dropping open connections. For more information about SQL sp_ commands click HERE. Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest Newer Post Older Post Home Subscribe to: Post Comments (Atom) Search The vTechie Blog Loading... View this blog in other languages Blog Archive ▼ 2014 (10) ► June 2014 (1) ► May 2014 (1) ▼ February 2014 (5) Detatch database failed for Server 'SQL-SERVER'. E... How to configure a CentOS Linux Server in 5 steps Resetting Windows Passwords How to Install the VMware Tools on CentOS Good Advice: Work hard, and invest in yourself ► January 2014 (3) ► 2013 (21) ► November 2013 (1) ► October 2013 (1) ► September 2013 (4) υ
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Help!! Database always In Recovery... SQL Server > SQL Server High Availability and https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ec453c49-7e27-49ad-993a-0ee91e6dd208/help-database-always-in-recovery?forum=sqldisasterrecovery Disaster Recovery Question 0 Sign in to vote Hi all, I had http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71113 to change the path of .mdf and .ldf files, so I decided to: 1) Take offline the database 2) run the quey ALTER DATABASE... MODIFY to change the path 3) Bring online the database. The last step hung up (with no errors) and left the database In Recovery. When I tried sql server to stop and restart sql server other databases changed their status In Recovery... Here is a dump of Errorlog files 2007-08-26 18:13:29.28 spid24s Starting up database 'DbOrdini'. ... ... 2007-08-26 18:13:30.09 spid24s * BEGIN STACK DUMP:2007-08-26 18:13:30.09 spid24s * 08/26/07 18:13:30 spid 242007-08-26 18:13:30.09 spid24s *2007-08-26 18:13:30.09 spid24s * Location: "logmgr.cpp":53342007-08-26 18:13:30.09 spid24s * Expression: !(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)2007-08-26 18:13:30.09 spid24s * SPID: cannot detach the 242007-08-26 18:13:30.09 spid24s * Process ID: 1380..... ..... 2007-08-26 18:13:30.40 spid24s Error: 17066, Severity: 16, State: 1.2007-08-26 18:13:30.40 spid24s SQL Server Assertion: File: <"logmgr.cpp">, line=5334 Failed Assertion = '!(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.2007-08-26 18:13:30.40 spid24s Error: 3624, Severity: 20, State: 1.2007-08-26 18:13:30.40 spid24s A system assertion check has failed. Check the SQL Server error log for details Could it be dangerous trying to kill this process ? If not, what is the best way do to it ? From Sql Server Activity Monitor (spid 24) or from Task Manager ? Thanks in advance Tuesday, August 28, 2007 7:37 AM Reply | Quote Answers 0 Sign in to vote try this... (a) Stop the service (c) Copy the MDF and LDF file to someother location (d) Delete the mdf and LDF file from the current location (e) restart the service (f) Attach the MDF & LDF file back to server Madhu Tuesday,
SQL Server experts to answer whatever question you can come up with. Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums SQL Server 2000 Forums SQL Server Administration (2000) Drop the database forcefully that uses by user Reply to Topic Printer Friendly Author Topic under2811 Constraint Violating Yak Guru India 366 Posts Posted-08/27/2006: 08:12:35 hello friendswith reference to forum disscission in subject : Creating Database at run time??????? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71099i created run time database and transfer my basic tables in that now i need to drop the database whose Domain user's licence expired that i decided on expiration periods...ok..but i am facing problem i wrote stored procedure Create Proc SP_DropDataBase@DBID varchar(100)asset @queryDB = 'use masterEXEC sp_detach_db 'DB_UserProfileIDTest'drop database DB_UserProfileID' + @DBID+ 'exec (@queryDB)but i am getting this error....Msg 3703, Level 16, State 2, Line 1Cannot detach the database 'DB_UserProfileIDTest' because it is currently in use.Msg 3702, Level 16, State 4, Line 3Cannot drop database "DB_UserProfileIDTest" because it is currently in use.I know that some one is using this database but how to detach this by force fully and drop the databaseT.I.A Edited by - under2811 on 08/27/2006 08:15:19 nr SQLTeam MVY United Kingdom 12543 Posts Posted-08/27/2006: 10:13:16 Why are you trying to detach and drop?You won't be able to run this sp in the database you are trying to drop as you will be a user in the database.Look at master..sysprocesses to get a list of all spids in the database and execute a kill command for them.When all have gone drop the database - it may take some time for the spids to drop.You would be able to do this for system spids.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. Topic Reply to Topic Printer Frie