none
Database is not accessible when treying to get it offline

    Question

  • i was requested to take a database offline.

    I pointed to my databse then right click --> tasks---> take offline.

    It seams that one user were still connected, i requested the users to log off.

    The taking offline took 3.5 hours ! then i had doughts something might go wrong, i pressed the close button to try to carry on the offline all over again. it failed as the database is in transition then i tried to rename it it also failed as database in transition with an error code: 952.

    I want to delete this database and build a new one (i have the backup and everything).

    Any advise?

    Monday, January 13, 2014 4:13 AM

Answers

  • in addition to above before running offline statement, kill the active database connection

    you can check that from sp_who2

    Kill SPID

    USE master
    GO

    ALTER DATABASE <db_name> SET OFFLINE WITH ROLLBACK IMMEDIATE


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, January 13, 2014 4:54 AM
  • You could have tried this

    Alter database database_name set restricted_user with rollback immediate

    GO

    Alter database database_name set offline with rollback immediate

    GO

    It is always better to remove all connections manually before setting it to offline. Check if there are any connections to the database and kill them. Its absolutely not necesary to delete the database for this simple issue.

    Monday, January 13, 2014 4:46 AM
  • i was requested to take a database offline.

    I pointed to my databse then right click --> tasks---> take offline.

    It seams that one user were still connected, i requested the users to log off.

    The taking offline took 3.5 hours ! then i had doughts something might go wrong, i pressed the close button to try to carry on the offline all over again. it failed as the database is in transition then i tried to rename it it also failed as database in transition with an error code: 952.

    I want to delete this database and build a new one (i have the backup and everything).

    Any advise?

    Hello,

    Advise

    1. When you are trying to make DB offline make sure app is not hitting it.

    2. Make sure no job is running which is making changes to the database.See job activity monitor 

    3. Always first make database in single user mode then take it offline.

    4. Use TSQL command to make Database  offline or online

    5. Run sp_who2 to check any running transaction in you database .You can also use sys.sysprocessess and filer out your DB.

    use master
    go
     alter database db_name set single_user with rollback immediate
     go
    alter database db_name set offline
    What is status of DB now can you please tell.Is it offline or still in process to go offline.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Monday, January 13, 2014 5:53 AM

All replies

  • You could have tried this

    Alter database database_name set restricted_user with rollback immediate

    GO

    Alter database database_name set offline with rollback immediate

    GO

    It is always better to remove all connections manually before setting it to offline. Check if there are any connections to the database and kill them. Its absolutely not necesary to delete the database for this simple issue.

    Monday, January 13, 2014 4:46 AM
  • in addition to above before running offline statement, kill the active database connection

    you can check that from sp_who2

    Kill SPID

    USE master
    GO

    ALTER DATABASE <db_name> SET OFFLINE WITH ROLLBACK IMMEDIATE


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, January 13, 2014 4:54 AM
  • i was requested to take a database offline.

    I pointed to my databse then right click --> tasks---> take offline.

    It seams that one user were still connected, i requested the users to log off.

    The taking offline took 3.5 hours ! then i had doughts something might go wrong, i pressed the close button to try to carry on the offline all over again. it failed as the database is in transition then i tried to rename it it also failed as database in transition with an error code: 952.

    I want to delete this database and build a new one (i have the backup and everything).

    Any advise?

    Hello,

    Advise

    1. When you are trying to make DB offline make sure app is not hitting it.

    2. Make sure no job is running which is making changes to the database.See job activity monitor 

    3. Always first make database in single user mode then take it offline.

    4. Use TSQL command to make Database  offline or online

    5. Run sp_who2 to check any running transaction in you database .You can also use sys.sysprocessess and filer out your DB.

    use master
    go
     alter database db_name set single_user with rollback immediate
     go
    alter database db_name set offline
    What is status of DB now can you please tell.Is it offline or still in process to go offline.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Monday, January 13, 2014 5:53 AM