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?

Jose Torres

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

Vitor Montalvão

Run this script to put the database offline disconnecting all current sessions:

ALTER DATABASE YourDatabaseNameHere
SET OFFLINE WITH ROLLBACK IMMEDIATE

Open in new window

Vitor Montalvão

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

qvfps

ASKER

Thanks. I have a meeting in a few minutes but I will try this once I am out and post the results.

Zac Harris

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

qvfps

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.

Vitor Montalvão

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.

qvfps

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.

Vitor Montalvão

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:

Zac Harris

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

qvfps

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

Vitor Montalvão

Can you post here the process that are running?

qvfps

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:

Jose Torres

Any spid between 1 and 50 are system processes.
Is there anything in the SQL Logs or the event logs?

Zberteoc

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.

qvfps

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.

Zberteoc

Why do you want to rename the files?

qvfps

ASKER

I wanted to make sure the database was not running so no applications could access it.

Vitor Montalvão

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.

Zberteoc

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.

qvfps

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.

Vitor Montalvão

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.

qvfps

ASKER

It was multiple system processes and I think they were using the sa account.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial