Database in Transition

  • I was trying to detach a database offline but the operation failed because there was an existing connection. Now, when I try to access the database I'm told that it does not exist and if I try to look at the properties I get the message that the database is in transition and to try the operation at a later time.

    How do I correct/remove the 'in transition' so I can detach the database?

    Tim

  • Can you post:

    - the exact SQL you used

    - the exact error message you received when trying to detach the database

    - the output from "select * from master.sys.databases where name='yourdbname';"

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • I was using SSMS to detach the database. I don't remember the exact error, but it wqas something along the line that the database could not be detached because there was one connection.

    The select results are:

    name CRMImport

    database_id 5

    source_database_id NULL

    owner_sid 0x0105000000000005150000007D5141DB214D18FA8D1CA28639220000

    create_date 34:33.5

    compatibility_level 90

    collation_name SQL_Latin1_General_CP1_CI_AS

    user_access 0

    user_access_desc MULTI_USER

    is_read_only 0

    is_auto_close_on 0

    is_auto_shrink_on 0

    state 0

    state_desc ONLINE

    is_in_standby 0

    is_cleanly_shutdown 0

    is_supplemental_logging_enabled 0

    snapshot_isolation_state 0

    snapshot_isolation_state_desc OFF

    is_read_committed_snapshot_on 0

    recovery_model 3

    recovery_model_desc SIMPLE

    page_verify_option 2

    page_verify_option_desc CHECKSUM

    is_auto_create_stats_on 1

    is_auto_update_stats_on 1

    is_auto_update_stats_async_on 0

    is_ansi_null_default_on 0

    is_ansi_nulls_on 0

    is_ansi_padding_on 0

    is_ansi_warnings_on 0

    is_arithabort_on 0

    is_concat_null_yields_null_on 0

    is_numeric_roundabort_on 0

    is_quoted_identifier_on 0

    is_recursive_triggers_on 0

    is_cursor_close_on_commit_on 0

    is_local_cursor_default 0

    is_fulltext_enabled 0

    is_trustworthy_on 0

    is_db_chaining_on 0

    is_parameterization_forced 0

    is_master_key_encrypted_by_server 0

    is_published 0

    is_subscribed 0

    is_merge_published 0

    is_distributor 0

    is_sync_with_backup 0

    service_broker_guid 4760655A-F09B-4AD4-9396-FC4FA82C0EF9

    is_broker_enabled 0

    log_reuse_wait 0

    log_reuse_wait_desc NOTHING

    is_date_correlation_on 0

    Tim

  • Tim, Paul's flying to China, so he might be delayed in responding.

    Have you restarted SQL? I wonder if this would clear up (roll forward/back) if SQL restarted.

  • Not flying till Saturday - don't write me off yet! 🙂

    Tim - this looks fine to me - the database state is ONLINE and MULTI_USER.

    Can you describe in more detail the problem you're seeing?

    If you want to forcibly detach the database, do the following first:

    ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    Caution: This forcibly rolls back user transactions and kills their connections.

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • hi tim, is this in same server?

    if so read this:-

    When you move database to another server and there are existing users in the detached database, you can lose users after attaching database on the new server. For example, if you move the Sales database from the Product server to the Test server (for the test purposes) and the user Alex exists in the Sales database, you should manually link the relationship between the Alex user and the appropriate login on the Test server.

    You can use the sp_change_users_login system stored procedure to link the specified user in the current database to the appropriate login. The following example links the user Alex in the current database to t he Alex login:

    EXEC sp_change_users_login 'Update_One', 'Alex', 'Alex'

  • This was on the database when I tried to do the detach itself.

  • While detaching the database did u drop the existing connections??

  • I imagine this was resolved some time ago, but I'll put relevant info here anyway as this just happened to me. A fix was found here:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125123&wa=wsignin1.0

    ...Simply closing down SSMS seemed to fix the problem. I did have another process hitting that database that was still able to query the database the whole time that the database was inaccessible from SSMS.

  • Hi,

    I have did the following steps and resolved the issue

    alter database dbname set offline

    alter database dbname set single_user with rollback immediate

    alter database dbname set offline

    Then refresh the db

    alter database dbname set multi_user with rollback immediate

    Regards,

    Arun karthikeyan.M

  • Microsoft SQL Server 2005 - 9.00.4266.00 (X64) Oct 7 2009 17:38:17

    Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    namexx_Test

    dbid46

    sid0x010500000000000515000000596FB2542C1A072CB776D02C046E0000

    mode0

    status4194328

    status21090519040

    crdate2010-09-03 15:24:58.030

    reserved1900-01-01 00:00:00.000

    category0

    cmptlevel90

    filenameF:\SQLData\xx_Test.mdf

    version611

    cannot detach

    cannot take offline

    cannot change to single user mode

    the run-away process is being killed and is showing as rollback but 0% progress by kill SPID command 2nd time.

    not sure what status this is: Status = 4194304 + 16 + 8

    restart sql server service, it went away.

  • After killing a process, if the database goes into transition just see the estimated rollback time by

    just running the same KILL statement of that SPID. There you can see the estimated rollback time in seconds

    wait till it is done. If you restart the server services,the database will automatically go into suspect mode due to rollback was abruptly stopped.

    Here are the steps to bring back a database from a Suspect mode in SQL 2005

    First bring the Database into Emergency Mode

    then run the DBCC checkdb (if you find any error repair it else)

    next alter database and set to single user mode

    then set the DB to multi user

  • That had been verified. The rollback was not going anywhere for hours. Some contractor issued "DROP ASSEMBLY [SqlClassLibrary]".... Instead waiting for hours, we have to get it back even from recovery because it was affecting the entire DEV/QA server and all people. The suspect mode was before restart, not after restart, if you call 4194328 suspect. The key is that particular status code 4194304 = autoshrink 16 = torn page detection 8 = trunc. log on chkpt

    AS I mentioned, none of those textbook commands work at the time,

    cannot set emergency mode

    DBCC checkdb does not return

    cannot set single_user or multi_user

    You may not have seen this until you see one.

    What I have not told you is someone turned on "autoshrink" on this dev host (too many monkeys in the kitchen), that background process cannot be killed. The drop command probably came from a untested script that developer did not know what that portion does.

  • Dear All,

    Just needs to kill the process, which is running on that database.

    Then Refresh the database is will goes in off line take is online and finished.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply