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?
qvfpsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jose TorresSenior SQL Server DBACommented:
This is a script I use to see whats on the system gives me a little more info and better format that sp_who
Run this query against master change the where clause for your database name and let us know what shows up.
/*
Display of Activity Monitor 2000
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
declare @sysprocesses table (
	[spid] [smallint] NOT NULL,
	[kpid] [smallint] NOT NULL,
	[blocked] [smallint] NOT NULL,
	[waittype] [binary](2) NOT NULL,
	[waittime] [int] NOT NULL,
	[lastwaittype] [nchar](32) NOT NULL,
	[waitresource] [nchar](256) NOT NULL,
	[dbid] [smallint] NOT NULL,
	[uid] [smallint] NOT NULL,
	[cpu] [int] NOT NULL,
	[physical_io] [bigint] NOT NULL,
	[memusage] [int] NOT NULL,
	[login_time] [datetime] NOT NULL,
	[last_batch] [datetime] NOT NULL,
	[ecid] [smallint] NOT NULL,
	[open_tran] [smallint] NOT NULL,
	[status] [nchar](30) NOT NULL,
	[sid] [binary](86) NOT NULL,
	[hostname] [nchar](128) NOT NULL,
	[program_name] [nchar](128) NOT NULL,
	[hostprocess] [nchar](8) NOT NULL,
	[cmd] [nchar](16) NOT NULL,
	[nt_domain] [nchar](128) NOT NULL,
	[nt_username] [nchar](128) NOT NULL,
	[net_address] [nchar](12) NOT NULL,
	[net_library] [nchar](12) NOT NULL,
	[loginame] [nchar](128) NOT NULL,
	[context_info] [binary](128) NOT NULL,
	[sql_handle] [binary](20) NOT NULL,
	[stmt_start] [int] NOT NULL,
	[stmt_end] [int] NOT NULL
)
INSERT INTO @sysprocesses
SELECT * FROM master.dbo.sysprocesses

SELECT
	spid as ProcessID
	,p.blocked as BlockedBy
	,isnull(b.Blocking,'') as Blocking
	,replace(convert(varchar, dateadd(second, datediff(second, login_time, getdate()), '1900-01-01 00:00:00.000'), 121),'1900-','') SessionDuration
	,case last_batch
		when '1900-01-01 00:00:00.000'
		then right(convert(varchar, dateadd(second, datediff(second, login_time, getdate()), '1900-01-01 00:00:00.000'), 121), 12) 
		else right(convert(varchar, dateadd(second, datediff(second, last_batch, getdate()), '1900-01-01 00:00:00.000'), 121), 12) 
	 end as 'BatchDuration'
	,case 
		when loginame = ' '  then RTRIM(nt_domain) + '\' + RTRIM(nt_username)
		else loginame
	end as [User]
	,DB_NAME([dbid]) as [Database]
	,[Status]
	,open_tran as OpenTransactions
	,cmd as Command
	,[PROGRAM_NAME] as [Application]
	,waittime as WaitTime
	,lastwaittype as WaitType
	,waitresource as [Resource]
	,CPU
	,physical_io PhysicalIO
	,memusage as MemoryUsage
	,login_time as LoginTime
	,last_batch as LastBatch
	,hostname as Host
from 
	@sysprocesses p
	left join (
		select blocked, COUNT(blocked) as Blocking 
		from @sysprocesses
		where blocked <> 0
		group by blocked 
	) b on p.spid = b.blocked
where 
	-- Exclude this SPID
	p.spid <> @@SPID
	-- Exclude system SPIDs
	AND p.spid > 50
	-- Use to filter by DATABASE
	AND DB_NAME([dbid]) = 'MyDatabase'
order by
	spid

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Run this script to put the database offline disconnecting all current sessions:
ALTER DATABASE YourDatabaseNameHere
SET OFFLINE WITH ROLLBACK IMMEDIATE

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

qvfpsAuthor Commented:
Thanks. I have a meeting in a few minutes but I will try this once I am out and post the results.
0
Zac HarrisSystems Administrator Commented:
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

ALTER DATABASE dbname SET OFFLINE ROLLBACK IMMEDIATELY;

Open in new window


*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;

Open in new window


*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;

Open in new window


*Replace dbname with your database name
0
qvfpsAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
qvfpsAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You're working with a SQL Server 2000 version and I don't have none to test.
Instead of taking offline can you detach the db? There should be an option to close all the active connections in the detach database screen. Here's how it looks like in SQL Server 2008R2:
DetachDB.PNG
0
Zac HarrisSystems Administrator Commented:
Try setting the DB into Single User Mode...

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

Open in new window

0
qvfpsAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post here the process that are running?
0
qvfpsAuthor Commented:
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:
0
Jose TorresSenior SQL Server DBACommented:
Any spid between 1 and 50 are system processes.
Is there anything in the SQL Logs or the event logs?
0
ZberteocCommented:
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.
0
qvfpsAuthor Commented:
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.
0
ZberteocCommented:
Why do you want to rename the files?
0
qvfpsAuthor Commented:
I wanted to make sure the database was not running so no applications could access it.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I wanted to make sure the database was not running so no applications could access it
You 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.
0
ZberteocCommented:
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.
0
qvfpsAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
qvfpsAuthor Commented:
It was multiple system processes and I think they were using the sa account.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What about renaming the database?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.