We have drive running out of space where the data and log file reside for the database in question on the server. I am trying to detach the files and move the log file to the different drive.
I am getting the error message that database can not be dropped as it is in use.
sp_detach_db 'order_management'
Server: Msg 3702, Level 16, State 1, Line 0
Cannot drop the database 'order_management' because it is currently in use.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Actually no one is using the database. Please let me know how to work around it. I put the db in single user mode also but didn't help.
Please help as the users are waiting to get back to the database.
I am trying to detach an SQL7 db and reattach it to an SQL2K server. When I run sp_who I get a list of 10-15 processes that are running, most under my sa login. If I kill all the others and then try to run sp_detach_db I get the following:
Server: Msg 3702, Level 16, State 1, Line 0 Cannot drop the database 'snb01' because it is currently in use. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I have detached and reattached before, but I don't recall what I did differently. Pls help. TIA D. Lewis
I am trying to detach and attach a database on SQL 7.0 Server with SP 2 through Enterprise manager . However if i right click o the Database and go to all task , i see the ' detach database' greyed out . The same thing is seen for ' attach Database 'when i try to attach the database .
Although it can done through T-SQL commands also , does any body know the reason why this field is greyed out ?
Does it depend on the SQL 7.0 Service pack or any kind of special permissions ? The same is visible on SQL 2000 Server Enterprise Manager .
Hi, we are imlementing a Data Warehouse on SQL 7.0. When we are loading new data into the Data Warehouse, I want to log off all the users connected to the Data warehouse and then start the loading.. How do I do it..Is there a way to log them off from a Batch file..I will be using the "sa" login for all loading..
I need to detach a Database, but with "EXEC sp_detach_db 'test','false'" I can't do this because it is in use. Is there a Possibility to make a "force detach" or something?
MSSQLSERVER VER : 2000 Using - Attach and Detach Database to move the database from one Server to another server. I did detached the database from Production and attached my test server, everything OK
Questions about - syslogins & sysdevices data dictionary tables 1. When I select * from sysdevices on production it shows all the database name size,phyname etc. After attaching to my test server I cannot see these things.. I know because it different master database but, how do I fix this. 2. syslogins - is there way to move all the syslogins after attaching to test server.
Please let me know if you guys have done anything on this.
hi Please help me .I have detached a database in sql server 2005 using object browser. Now i cant see the database in object browser of sql server management studio .I tried to attach it but the mdf file of that database is missing in the data folder. i had not created the backup. Can any one help me how to reattach that database.I will be very thankful to him..
There are two databases on two web servers, db01 is on server01, db01_replica is on server02, db02 is on server02, db02_replica is on server01. db01 and db02 are both for one system. Each time after doing performance test, I have to recover databses. I copy the data files in a folder, try to use detach and attach to recover databases. But with two replication dbs, I don't know how to do it. The replication db should also be recovered.
I'm using following connection string in my application in (C# 2.0 windows )
Data Source=.SQLEXPRESS; Trusted_Connection=yes;AttachDbFilename=|DataDirectory|myDB.mdf"
My application attaches the file successfully when the first connection is established, but after closing my application the database is remained in the sql server express and it does not detached automatically. According to this article the database shall be detached and I need it in my application because of the security.
I've just created ASPNETDB database with ASP.NET Security. Now, I want to send this db to orther computer.First, I detached this db, then when I used attach database in that computer, there is an error :Error 602: Could not find row in sysindexes for database ID 8, object ID 1, indext ID 1. RUN DBCC CHECKTABLE on sysindexes. Please help me .Thank.
I have developed a db on a local machine (98) and would like to move it to another machine running nt 4 where it will function as a production db. I am mapped to see the other server (called "labnt"). The drive on "labnt" is partitioned, with the c drive holding programs, and the e drive designed to hold the data.
I would like to move the db (called SNB01) to the MSSQLData subdirectory on the e-drive. I have tried the following sproc to detach:
EXEC sp_detach_db 'SNB01', 'true'
And the following to reattach: EXEC sp_attach_db @dbname = 'SNB01', @filename1 = 'labntewmssqldataSNB01_Data.mdf', @filename2 = 'labntewmssqldataSNB01_log.ldf'
The above path was given me by our network admin guy.
QA gives the following error: Server: Msg 5105, Level 16, State 4, Line 1 Device activation error. The physical file name 'labntewmssqldataSNB01_Data.mdf' may be incorrect.
In SQL when I right click on Databases the option to use Attach/Detach is greyed out. I don't understand why. I'm running SQL 7 on Win2000 server and I have Win2k at sp2 and SQL at sp2.
Is it possible to detach a database on SQL Server 2000 and attach it to SQL Server 7 ? I know it's fine to do the other way around. Also, any adverse effects I should be aware of ?
I need some insight if possible. I installed a new server (to replace an aging machine) with SQL 2000. After installing SQL on the new server I tried to detach a database from the old server so that I can attach it to the new instance of SQL. My problem is that when I do the process I am stopped with an error message.
“Error 8146: procedure sp_detach_db has no parameters and arguments supplied.”
This is happening with all of the user databases on the old server.
I have tried to run this using both the Query Analyzer and from the right-click menu option and both return the same error message. Any help would be greatly appreciated.
I want to move a database instance (about 50 user databases) to another server, both are running SQL Server 2000. Which method is better, retore from backup files or detach/attach? Some papers said restore amybe cause incorrect login and password transfer. Is it true?
An original database (eg A.mdf) is imported/exported to a different server as named B.mdf. The detach of A.mdf was unitentionally not done. It seems that both the database were updated.
I'm trying to detach a database and reattach to another server. When trying to detach it using Enterprise Manager, the command is greyed out. I've tried taking the database offline first but the command is still not enabled. I'm logged in as sysadmin. What gives?
I open enterprise manager , right click and choose detach detach database. I see line : connections using this database. I think that this is connection from client connect to database.I feel that connection disappear automatically after time interval. I want to know how long time ? Thank you very much.
We have C:, D: and E: drives on our server box. C: drive ispartitioned and is big enough only to hold Operating system files. D:and E: drives are what were supposed to be used by developers / dba'sto store / create SQL Server (system and user databases).Well, some developers installed the entire SQL Server named instanceand their system and user defined databases on the C: drive. Is thereany way to move the system databases (master, msdb, distributionetc.,) from the C: to the D: / E: drives?Appreciate any feedback.ThanksJagannathan Santhanam
My purpose is to detach a user instance. This code reaches timeout quite often and doesn't work reliably on Vista. It times out both on the alter database and the detach. I need this to work perfectly every time. How can I fix it? It does work sometimes but I have not detected a pattern.
public void DetachDatabase(string filePath, string serverName) { SqlConnection conn = new SqlConnection();
// Need to set single user otherwise cannot detach the database because it is currently in use. string c1 = "USE master; ALTER DATABASE [" + filePath + "] SET SINGLE_USER;";
SqlCommand cmd = new SqlCommand(); cmd.Connection = conn;
I created a db in ssms; I detached it and moved it into a subdirectory of a vb app. Now sql server says the database does not exist. If I recreate the database in smss, I can run an INSERT INTO using that db as the destination. However, this only works if I keep it in the mssql data directory. How can I move it to the apps subdirectory so that it can be deployed with the app and still work? BTW, I also tried creating the databases in VS2005 with same results.
This is the connection string (tried this and non-express version with same results):
I'm not sure if this is the right place for this but I have an issue.
I have a SQL2005 server with SAN drives for the MDF/LDF files. I have updated recently to SP2.
When I detach a Dbase using the appropriate command it strips the Security on the file level (which SP2 is supposed to fix) and I am unable to attach the database.
I am using a domain account to do this with from the query analyser window.
I have the need to detach a database from code, at the moment I am using 'SSEUTIL -d' for this in my code and it works OK but now I want to change to using an SQLCommand. Here is my code so far:
Code Snippet
Dim conDatabase As SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=MyMDFPath"; Integrated Security=True;Connect Timeout=60;User Instance=True") Dim comDetach As New SqlCommand("EXEC master.dbo.sp_detach_db '" + MyMDFPath + "'", conDatabase) conDatabase.Open() comDetach.ExecuteNonQuery() conDatabase.Close()
However, when I execute this I always get the following exception:
Code Snippet
Cannot detach the database 'MyMDFPath' because it is currently in use.
It seems obvious to me that the problem is that to do the detach I create a connection - how do i get around this?
I asked this question in the Tools General forum but received no response. Does anybody is this forum know how to resolve this problem? Here it is again with a bit more info:
When I ran sseutil -l, I discovered several databases that were still attached. However, they were old test projects that had previously been deleted. How do I detach them if they no longer exist? Using the detach command will not work because it just gives me a message: No valid database name matches the value specified.
There are two databases on two web servers, db01 is on server01, db01_replica is on server02, db02 is on server02, db02_replica is on server01. db01 and db02 are both for one system. Each time after doing performance test, I have to recover databses. I copy the data files in a folder, try to use detach and attach to recover databases. But with two replication dbs, I don't know how to do it. The replication db should also be recovered.
I'm trying to detach a database and after archive it for deliver to other server.
The detach wents fine and the database is removed from the database tree list in Management Studio. The problem is that the mdf file is being locked by some process (maybe SQL) and I can't imagine why. Here is the code for this operation:
USE master;
GO
ALTER DATABASE IMS_MCK_MIS SET AUTO_UPDATE_STATISTICS ON
Sometimes you have some columns in the dataflow that you at some point in the flow doesn't need in the further flow. So maybe a tast or an option for disabling columns that are no longer in use
