qvfps
asked on
How to unlock datbase in SQL2000
I am running SQL2000 on a Windows 2003 server. I am trying to migrate the databases to a newer server. I have one database I have moved and to verify everything is correct and I have nothing pointing to it I want to take it offline. Every time I try I receive the following message
Error 5070: Database state cannot be changed while other users are using the database.
I executed SP_WHO2 and there are 4 connections to the database with a status of Background and a command of TASK MANAGER
If in SQL Server Enterprise Manager I go to Management-Current Activity and look in Locks/Object I see the 4 Process IDs. If I look at the properties the Last TSQL command batch is empty and if I select Kill Process nothing happens. If I refresh or exit and get back in it is still there. I have the same results if I try it from Locks /Process ID.
I have looked in scheduled tasks, Under SQL Server Agent and I can not find anything which should be accessing the database
How do I identify what is using the database and cancel it so I take it offline?
Error 5070: Database state cannot be changed while other users are using the database.
I executed SP_WHO2 and there are 4 connections to the database with a status of Background and a command of TASK MANAGER
If in SQL Server Enterprise Manager I go to Management-Current Activity and look in Locks/Object I see the 4 Process IDs. If I look at the properties the Last TSQL command batch is empty and if I select Kill Process nothing happens. If I refresh or exit and get back in it is still there. I have the same results if I try it from Locks /Process ID.
I have looked in scheduled tasks, Under SQL Server Agent and I can not find anything which should be accessing the database
How do I identify what is using the database and cancel it so I take it offline?
Run this script to put the database offline disconnecting all current sessions:
ALTER DATABASE YourDatabaseNameHere
SET OFFLINE WITH ROLLBACK IMMEDIATE
Forgot to tell you that you shouldn't run that command under your database context but in another one. Best to run under master:
USE master
GO
ALTER DATABASE YourDatabaseNameHere
SET OFFLINE WITH ROLLBACK IMMEDIATE
ASKER
Thanks. I have a meeting in a few minutes but I will try this once I am out and post the results.
If you want to force active users and processes to disconnect from the database and rollback their active transactions you can use the following clause
*Replace dbname with your database name
You can also put in a wait variable in the event that you want to give the users a certain amount of time to finish up
*Replace dbname with your database name and replace XX with the number of seconds you want to wait.
After the command completes you should see the following:
To bring the database back online:
*Replace dbname with your database name
ALTER DATABASE dbname SET OFFLINE ROLLBACK IMMEDIATELY;
*Replace dbname with your database name
You can also put in a wait variable in the event that you want to give the users a certain amount of time to finish up
ALTER DATABASE dbname SET OFFLINE ROLLBACK AFTER XX SECONDS;
*Replace dbname with your database name and replace XX with the number of seconds you want to wait.
After the command completes you should see the following:
Nonqualified transactions are being rolled back. Estimated rollback completion:
100%.
To bring the database back online:
ALTER DATABASE dbname ONLINE;
*Replace dbname with your database name
ASKER
I ran the Activity Monitor Script above and it doesn't show anything for the database. I removed "AND DB_NAME([dbid]) = 'MyDatabase' " from the query and it shows some access against other databases but nothing for the one I am trying to take offline.
That means the sessions were killed. Sometimes take longer if they need to be rolled back.
So you can try now to put it offline.
So you can try now to put it offline.
ASKER
Even though the script above doesn't show anything accessing the DB I still can not take it offline. I still get the same error message and I still show the same locks in Enterprise manager.
I tried to run ALTER DATABASE "MYDB" SET OFFLINE ROLLBACK IMMEDIATELY and I kept getting syntax errors until I removed the ROLLBACK option.
When I execute ALTER DATABASE "MYDB" SET OFFLINE I don't get any errors, it just keeps running. IT has been executing for over 8 minutes now.
I tried to run ALTER DATABASE "MYDB" SET OFFLINE ROLLBACK IMMEDIATELY and I kept getting syntax errors until I removed the ROLLBACK option.
When I execute ALTER DATABASE "MYDB" SET OFFLINE I don't get any errors, it just keeps running. IT has been executing for over 8 minutes now.
Try setting the DB into Single User Mode...
Use transact sql:
Use transact sql:
USE master;
GO
ALTER DATABASE dbname
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE dbname
SET READ_ONLY;
GO
ALTER DATABASE dbname
SET MULTI_USER;
GO
ASKER
When I try to clear the connections in the detach datbase window I get a message saying:
Error 6107: Only user process can be killed
Error 6107: Only user process can be killed
Can you post here the process that are running?
ASKER
there are 4 identical tasks. The information from SP_WHO2 is below. The only fields which are different are SPID, CPUTime and DISKIO.
SPID: 11-14
Status: Background
Login: sa
HostName: *
BlkBy: *
DBName: MYDB
Command: TASK MANAGER
CPUTime: 0-15
DiskIO: 10-100
LastBatch: 11/10 23:58
ProgramName:
SPID: 11-14
Status: Background
Login: sa
HostName: *
BlkBy: *
DBName: MYDB
Command: TASK MANAGER
CPUTime: 0-15
DiskIO: 10-100
LastBatch: 11/10 23:58
ProgramName:
Any spid between 1 and 50 are system processes.
Is there anything in the SQL Logs or the event logs?
Is there anything in the SQL Logs or the event logs?
Turn off the SQL server service and then copy the files and attached then on the new server.
Another solution is to backup the database and restore it to the new server.
Another solution is to backup the database and restore it to the new server.
ASKER
There was nothing I could find in any of the logs. I ended up waiting until evening, stopping SQL Server and renaming the MDF and LDF files before starting it back up.
Why do you want to rename the files?
ASKER
I wanted to make sure the database was not running so no applications could access it.
I wanted to make sure the database was not running so no applications could access itYou could stop SQL Server instance to achieve this but of course that should be the only database available in the instance otherwise the rest of the databases would be inaccessible as well.
You can only rename/move the database files if either database is taken offline or if you stop the server. Either way the database cannot be used.
ASKER
That is what I was trying to do. I could not bring down the server because there are other active databases on it. I could not take it offline or disconnect the DB. It gave me an error every time I tried.
I just wanted to make sure that nothing could access the database.
I just wanted to make sure that nothing could access the database.
I just wanted to make sure that nothing could access the database.Remove all users from the database. Without an user a database can't be accessed unless you're using a sa login.
ASKER
It was multiple system processes and I think they were using the sa account.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Run this query against master change the where clause for your database name and let us know what shows up.
Open in new window