Post reply

Force delete database entry from sys.sysdatabases

  • February 1, 2021 at 8:38 am

    Go to Answer

    #3839538

    Hi All,

    Need some piece of advice on below situation.

    We are using SQL 2017 RTM CU22.

    One of the database is mounted on a Third party storage solution called Actifio.

    We are unable to drop one database.

    During server patching windows over the weekend, something happened and some of the database mount points became inaccessible for few databases.  We worked with 3rd party storage vendor Actifio and we were able to bring some db's online.

    However, one of the database went into Recovery pending state. The contents of one of the data files, got corrupted.

    This is a non-prod environment and so we ran check db with repair allow data loss. The database is brought online with inconsistent data. So now we wanted to do a db refresh from prod again. For this, we need to unmount the database from Actifio tool. We tried the unmount, the disks were unmounted , however the db was not deleted at SQL Server. We can see the database still under sql instance.

    We tried below list of options but no luck.

    • Cannot drop a database.
    • Cannot rename the database.
    • Cannot dettach the database.
    • Cannot bring the database offline.
    • Cannot able to take the database to single user mode.

    In SQL Server 2017, is there a way to forcefully remove entry of the database name is sys.databases ?

    For App team, we can do the db refresh with a new db name but wanted to know is there a way to remove the STALE Database entry.

    Looking for a way to somehow delete the entry. If anyone has tried any brute force method of getting rid of database entry in system dmv's , please let me know.

    Thanks,

    Bob

  • February 1, 2021 at 11:28 am

    Answer

    #3839584

    Bring up the new drives, restore the database as a new name.

    Stop SQL Server, copy the .mdf and .ldf files someplace.

    Start SQL Server, drop the re-named database.

    Run this script

    USE master;
    GO
    ALTER DATABASE YourDatabase
    MODIFY FILE
    (
        NAME = logical data file name,
        FILENAME = N'c:\YourDataFile.mdf'
    );
    GO

    USE master;
    GO
    ALTER DATABASE YourDatabase
    MODIFY FILE
    (
        NAME = logical log file name,
        FILENAME = N'c:\YourLogFile.ldf'
    );
    GO

    Copy or move the previously copied files to the file location in the script.

    Rename them to what you want them to be.

    Re-start SQL and see what happens.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • February 3, 2021 at 10:04 am

    #3840449

    Thanks Michael. After sql restart, the database went to Recovery pending state and we were able to drop the database.

Viewing 3 posts - 1 through 2 (of 2 total)

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