Exclusive accessn cannot be obtained while restoring DB

Hi,
I keep getting this error while trying to restore the
database:
"Exclusive Access could not be obtained because the
database is in use. RESTORE database is terminating
abnormally."
I am sure though that noone has logged onto the system
while I doing the restore but I keep getting this error.
Any clue?
Thanks.use sp_who to see if there is any connection made to the database. Also you
can trying running command through query analyzer.
alter database <db_name> set single_user with rollback immediate.
above command will kill all the session with rolling back on going
transactions for the particular database. then run the restore command with
the same session (since your database is in single user mode).
-Vishal
"John" <kumarj@.logica.com> wrote in message
news:08ed01c33f0c$4c0c7c70$a301280a@.phx.gbl...
> Hi,
> I keep getting this error while trying to restore the
> database:
> "Exclusive Access could not be obtained because the
> database is in use. RESTORE database is terminating
> abnormally."
> I am sure though that noone has logged onto the system
> while I doing the restore but I keep getting this error.
> Any clue?
> Thanks.
>|||John,
Are you trying to restore via enterprise manager?Execute sp_who in Query
analyzer and make sure the concerned databasename is not mentioned in the
output.
If you're running SQL Server 2000, you can issue this command to remove all
connections to the database :
ALTER DATABASE <databasename> SET single_user WITH ROLLBACK IMMEDIATE
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"John" <kumarj@.logica.com> wrote in message
news:08ed01c33f0c$4c0c7c70$a301280a@.phx.gbl...
> Hi,
> I keep getting this error while trying to restore the
> database:
> "Exclusive Access could not be obtained because the
> database is in use. RESTORE database is terminating
> abnormally."
> I am sure though that noone has logged onto the system
> while I doing the restore but I keep getting this error.
> Any clue?
> Thanks.
>

Exclusive access could not be obtained because the database is in use.

I have a test database that is automatically restored from the live database each day at 2:30AM. I recently started receiving this message:

Exclusive access could not be obtained because the database is in use.

and the restore is not able to occur.

Any ideas on how I can prevent this from happening?

You can't restore a db when it is in use, meaning that there are connections open to the database. Somehow, somewhere, connections are being left open to your database. Before you leave for the day, run the following:

exec sp_who

This will tell you who (username and machine name) has an open connection to the database.

Exclusive access could not be obtained because the database is in use.

How to close the existing connections to a particluar database in sql server. Please note that i donot want to start stop sql server. I just want to close the existing connections so that i can do a restore on that database programatically.

I am using sqldmo for this purpose. Does anyone knows how to do that with sqldmo or is there any other method??

Waiting for your earliest repliessp_who2
followed by
kill <process id>

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

sql

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?