Post reply

Cannot delete db on sqlserver

  • November 22, 2010 at 8:46 pm

    #243195

    hello to all

    i would like to ask if ever their is a possible solutions on this particular problem, here the scenario we have a db name test on the test server and accidentally deleted where the file is being stored without detaching first on the sqlserver, now our problem is we cannot able to delete the database test on the sqlserver and his status is in recovery mode.."The physical file is deleted"..please help..

    thanks

  • November 22, 2010 at 9:04 pm

    #1254159

    If you have a backup, then restore it, then delete it. Sorry, I just reread your statement. Did you delete the file or the whole drive where the file lived?

  • November 22, 2010 at 9:09 pm

    #1254161

    yes the mdf and log file deleted on the drive where he is stored..

  • November 22, 2010 at 9:11 pm

    #1254162

    we dont need that database anymore but it causes our server to hung up..how can we detach that database on the sqlserver..

    thanks

  • November 22, 2010 at 9:49 pm

    #1254165

    Ok.

    How did you manage to delete the DB when it was online and in use. I just tested your theory and could not delete the individual files while the DB was online.

    So I took the DB offline and then managed to delete the files.

    then I could delete the DB.

    What state is your DB in at present? Have you tried taking it offline?

  • November 22, 2010 at 10:36 pm

    #1254176

    i asked again the person who did this, he told me that he took the db to offline then he delete the file manually where it was stored and then he refresh the sqlserver there it shows a gray color..

    thanks

  • November 23, 2010 at 12:58 am

    #1254215

    jolan.mahinay (11/22/2010)

    he took the db to offline then he delete the file manually where it was stored and then he refresh the sqlserver there it shows a gray color..

    it means that deletion has been done intentionally.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • November 23, 2010 at 2:01 am

    #1254231

    whether it is intentionally or accidentally what we need is the solutions if there is..if there is none then we will going to reinstall the sqlserver that's our last option to get rid that scenario.

    thanks

  • November 23, 2010 at 2:58 am

    #1254261

    Did you try detaching the database or dropping the database?

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • November 23, 2010 at 3:07 am

    #1254264

    put the files back, start the DB and then delete properly

    failing that, try to restore a DB over that one, same name, but moving the files, then delete properly.

  • November 23, 2010 at 3:31 am

    #1254281

    tried to simulate your situation.

    create database testdd on primary(name='testdd', filename='c:\testdd.mdf')

    log on (name ='testddlog', filename='c:\testddlog.ldf')

    -- setting database to offline mode

    alter database testdd set offline

    -- now deleting mdf file

    xp_cmdshell 'del "c:\testdd.mdf"'

    -- trying to bring db online

    alter database testdd set online -- you get error here

    drop database testdd -- database is dropped successfully


    Pradeep Singh

  • November 23, 2010 at 3:47 am

    #1254289

    sir we restore the file from the bin then the status of the db is in recovery\read only, what would be the next step cause when we try to right click that db the server hung up so we tried to wait until its recovered but we waited for almost 6 hrs but no luck still on its status of in recovery\read only, the mdf is only about 1 gig.

    thanks

  • November 23, 2010 at 3:49 am

    #1254297

    we could not execute detach or dropping because the db is on recovery\read only mode, and we tried also the sp_detach_db but no luck..

    thanks

  • November 23, 2010 at 3:51 am

    #1254300

    jolan.mahinay (11/23/2010)

    we restore the file from the bin then the status of the db is in recovery\read only, what would be the next step cause when we try to right click that db the server hung up so we tried to wait until its recovered but we waited for almost 6 hrs but no luck still on its status of in recovery\read only, the mdf is only about 1 gig.

    can u post the command used to restore the database?

    also can u run this command?

    restore database yourdb with recovery


    Pradeep Singh

  • November 23, 2010 at 1:38 pm

    #1254721

    Have you tried doing a restore with REPLACE? You should be able to use any database backup as long as you make the database name match and use WITH MOVE to put the files in the right place with the right names.

    If this doesn't work youmay need to retore the database to a new location with a new name, then take it off line and COPY (this is very important other wise you just have the same issue again) the files to the old location, then restart the service.

    Cheers

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Viewing 15 posts - 1 through 15 (of 20 total)

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