Database restoration error: "unable to obtain exclusive access to the database because the database is in use" SOLUTION

Database restoration error: "unable to obtain exclusive access to the database because the database is in use" SOLUTION

When you restore a database, you may be prompted that you cannot obtain the exclusive access to the database because the database is in use !!
In this case, the thread that is using the database needs to be killed before restoring the database.

This solution uses sysprocesses in the system table. To access sysprocesses, you must locate the master database.

The following are the threads that are using the 'v091222' database:

Use master

Declare @ dbname varchar (20)

Set @ dbname = 'v091222'

 

Declare @ SQL nvarchar (500)

Declare @ spid int -- The spid value is a unique integer assigned to the connection when the user connects.

Set @ SQL = 'Clare getspid cursor

Select spid from sysprocesses where dbid = db_id (''' + @ dbname + ''')'

Exec (@ SQL)

Open getspid

Fetch next from getspid into @ spid

While @ fetch_status <>-1 -- if the fetch statement does not fail to be executed or this row is not in the result set.

Begin

Exec ('Kill '+ @ spid) -- terminate a normal connection

Fetch next from getspid into @ spid

End

Close getspid

Deallocate getspid

 

 

Database sysprocesses table details

Sysprocesses
The sysprocesses table stores information about processes running on Microsoft & reg; SQL Server. These processes can be client processes or system processes. Sysprocesses is only stored in the master database.

Column name data type description
Spid smallint SQL Server process ID.
Kpid smallint Microsoft Windows NT 4.0 & reg; thread ID.
The ID (spid) of the blocked smallint block process ).
Waittype binary (2) is retained.
Waittime int Current wait time (in milliseconds ). When the process is not waiting, it is 0.
Lastwaittype nchar (32) indicates the string of the last or current waiting type name.
Waitresource nchar (32): The signed representation of the lock resource.
Dbid smallint the ID of the database currently being used by the process.
UID smallint user ID for executing commands.
The cumulative CPU time of the CPU int process. Whether the set statistics time on option is on or off, this entry is updated for all processes.
Accumulative disk read and write of physical_io int process.
The number of pages in the cache during which memusage int is currently allocated to the process. A negative number indicates that the process is releasing the memory allocated by another process.
Login_time the time when the login_time datetime client processes log on to the server. System processes are used to store the time when SQL Server is started.
Last_batch the time when the client process last executed a remote stored procedure call or execute statement. System processes are used to store the time when SQL Server is started.
ECID smallint uniquely identifies the execution context ID of the sub-thread that represents the operation of a single process.
The number of opened transactions of the open_tran smallint process.
Status nchar (30) process ID status (such as running and sleep ).
Sid binary (85) User's globally unique identifier (guid ).
Hostname nchar (128) workstation name.
Program_name nchar (128) ApplicationProgram.
Hostprocess nchar (8) wks process ID.
CMD nchar (16) the Command currently being executed.
Nt_domain nchar (128) client's Windows NT 4.0 Domain (If Windows authentication is used) or the Windows NT 4.0 Domain that trusts the connection.
Nt_username nchar (128) indicates the Windows NT 4.0 username of the process (If Windows authentication is used) or the Windows NT 4.0 username that trusts the connection.
Net_address nchar (12) is assigned a unique identifier for the network interface card on each user workstation. When a user logs on, This identifier is inserted into the net_address column.
Net_library nchar (12) is used to store the column of the client network library. Each client process enters the network connection. The network connection has a network library associated with these processes, which enables these processes to establish connections. For more information, see client and server net-library.
Loginame nchar (128) login name.