Database is in Transistion. There is already an open datareader associated with this data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jc - 22 Jul 2008 19:44 GMT

SQL 2005.

I tried to take a DB offline but it just never came back.

I now just want to detach and/or delete it it.. but get error:

"There is already an open datareader associated with this Data"

What's the best way to remove connections from a DB from the command
line.

How can I now remote that open datareader?

Thanks for any help or information.

Reply to this Message

Alex Kuznetsov - 22 Jul 2008 20:29 GMT

> SQL 2005.
>
[quoted text clipped - 6 lines]
> What's the best way to remove connections from a DB from the command
> line.

ALTER DATABASE YourDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Reply to this Message

Eric Russell - 22 Jul 2008 20:47 GMT

There are many ways to list what connections are active on a server. One way
is to use sp_who2, and you can use KILL <SPID> to terminate the process.
Also, it is possible to set a database to RESTRICTED_USER mode while
simultaneously disconnecting all connections and aborting any active
transactions. The following will disconnect all connections without active
transactions and allow 2 minutes for any currently active transactions to
complete. Once done, the database will be in RESTRICTED_USER mode, meaning
that only the sysadmin or members of the DBO group can access it. Once the
maintenace operation has completed, restore the database to MULTI_USER mode.

ALTER DATABASE database-name SET RESTRICTED_USER WITH ROLLBACK AFTER 120
SECONDS

> SQL 2005.
>
[quoted text clipped - 10 lines]
>
> Thanks for any help or information.

Reply to this Message