locked
Unable to drop database RRS feed

  • Question

  • I'm recently encountered database drop issue.

    I'm trying to drop DB using SYSADMIN role. Few days back one of the DB was not accessible and tried to expand the DB and it was thronging error "The Database ABC is not accessible".

    now trying to drop the db getting error 

    Msg 5011, Level 14, State 7, Line 1
    User does not have permission to alter database 'ABC', the database does not exist, or the database is not in a state that allows access checks.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.
    Msg 3702, Level 16, State 4, Line 2
    Cannot drop database "ABC" because it is currently in use.

    i'm the SYSADMIN role on SQL Server.

    How can is drop the DB?.

    • Changed type Olaf HelperMVP Tuesday, May 12, 2020 7:08 AM More a question then a discussion
    Tuesday, May 12, 2020 6:31 AM

Answers

  • Hi Baraiya Kirit,

    If you really want to drop the database try workaround as next:

    1.stop the sql engine service;

    2.delete the .mdf and .ldf files;

    3.Restart the sql engine service.

    Test as next:

    1.Test:

    2.Stop the sql engine service and then Delete the .mdf and .ldf files of ABC;  

    3.restart the sql engine service and try code as

    Use master

    Drop database ABC

    4.Refresh and check whether there is the ABC;

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Baraiya Kirit Wednesday, May 13, 2020 9:07 AM
    Wednesday, May 13, 2020 1:52 AM

All replies

  • or the database is not in a state that allows access checks.

    First check the state of the database with

    select name, state_desc
    from sys.databases

    What's the result?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, May 12, 2020 7:10 AM
  • ABC database is online.
    Tuesday, May 12, 2020 7:33 AM
  • Cannot drop database "ABC" because it is currently in use.

    Then close all connection to the database, e.g. setting the database in single user or admin only mode.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, May 12, 2020 7:35 AM
  • none of the operation allowed on ABC Database. There is no active connection and not able open the property as well.
    Tuesday, May 12, 2020 7:42 AM
  • Hi Baraiya Kirit,

    Use master
    Drop database ABC

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 12, 2020 8:12 AM
  • Hi Yuxi,

    Through an error:

    Msg 3702, Level 16, State 4, Line 2
    Cannot drop database "ABC" because it is currently in use.

    As there is no active connection on DB and not accessible DB.

    Tuesday, May 12, 2020 8:18 AM
  • Hi Baraiya, try the following snippet in your SSMS:




    declare @sSql nvarchar(max);

    select distinct @sSql =

    (



        select 'kill ' + cast(session_id as nvarchar(4)) as [data()]
        from sys.dm_exec_sessions
        where session_id <> @@SPID
        and is_user_process = 1
        for xml path('')
        );

        exec (@sSql);

    Tuesday, May 12, 2020 9:03 AM
  • Tried given query with drop command the database but same error:

    ALTER DATABASE [swinds] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    USE [master]
    GO
    DROP DATABASE [swinds]
    GO

    Msg 5011, Level 14, State 7, Line 1
    User does not have permission to alter database 'ABC', the database does not exist, or the database is not in a state that allows access checks.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.
    Msg 3702, Level 16, State 4, Line 2
    Cannot drop database "ABC" because it is currently in use.

    Tuesday, May 12, 2020 9:17 AM
  • Try to run the following query in your SSMS session:


    declare @sSql nvarchar(max);

    select distinct @sSql =

    (



        select 'kill ' + cast(session_id as nvarchar(4)) as [data()]
        from sys.dm_exec_sessions
        where session_id <> @@SPID
        and is_user_process = 1
        for xml path('')
        );

        exec (@sSql);
    Tuesday, May 12, 2020 9:19 AM
  •  Hi,

     Try the solution offered by Aaron Bertrand  from the similar thread SQL Server Cannot drop database <dbname> because it is currently in use… but no sessions displayed.

    Tuesday, May 12, 2020 9:20 AM
  • On ABC db there are 2 active system connection connections ie. SPID 5 and 27.

    Tried to kill these 2 SPID but unable to kill because its system SPID.

    How can I kill theses connection?

    Tuesday, May 12, 2020 9:46 AM
  • Please check what two sessions were are waiting for - any blocking 

    SELECT
        [owt].[session_id] AS [SPID],
        [owt].[exec_context_id] AS [Thread],
        [ot].[scheduler_id] AS [Scheduler],
        [owt].[wait_duration_ms] AS [wait_ms],
        [owt].[wait_type],
        [owt].[blocking_session_id] AS [Blocking SPID],
        [owt].[resource_description],
       
        [eqmg].[dop] AS [DOP],
        [er].[database_id] AS [DBID],

        [eqp].[query_plan],
        [est].text
    FROM sys.dm_os_waiting_tasks [owt]
    INNER JOIN sys.dm_os_tasks [ot] ON
        [owt].[waiting_task_address] = [ot].[task_address]
    INNER JOIN sys.dm_exec_sessions [es] ON
        [owt].[session_id] = [es].[session_id]
    INNER JOIN sys.dm_exec_requests [er] ON
        [es].[session_id] = [er].[session_id]
    FULL JOIN sys.dm_exec_query_memory_grants [eqmg] ON
        [owt].[session_id] = [eqmg].[session_id]
    OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
    OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
    WHERE
     [owt].[session_id] in(5,27)
    ORDER BY
        [owt].[session_id],
        [owt].[exec_context_id];
    GO


    http://uk.linkedin.com/in/ramjaddu

    Tuesday, May 12, 2020 11:56 AM
  • Hi Baraiya Kirit,

    If you really want to drop the database try workaround as next:

    1.stop the sql engine service;

    2.delete the .mdf and .ldf files;

    3.Restart the sql engine service.

    Test as next:

    1.Test:

    2.Stop the sql engine service and then Delete the .mdf and .ldf files of ABC;  

    3.restart the sql engine service and try code as

    Use master

    Drop database ABC

    4.Refresh and check whether there is the ABC;

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Baraiya Kirit Wednesday, May 13, 2020 9:07 AM
    Wednesday, May 13, 2020 1:52 AM