January 17, 2008 at 7:11 am
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
January 17, 2008 at 7:52 am
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
January 17, 2008 at 8:36 am
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
January 17, 2008 at 9:28 am
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.
January 17, 2008 at 10:07 am
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
January 21, 2008 at 4:20 pm
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'
January 22, 2008 at 5:03 am
This was on the database when I tried to do the detach itself.
January 22, 2009 at 11:02 pm
While detaching the database did u drop the existing connections??
July 27, 2009 at 11:23 am
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.
December 18, 2009 at 6:48 am
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
November 9, 2010 at 7:19 am
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.
Jason
http://dbace.us
😛
November 9, 2010 at 7:33 am
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
November 9, 2010 at 8:01 am
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.
Jason
http://dbace.us
😛
January 27, 2011 at 9:34 pm
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