Database with no visible name

  • I have a database attached to SQL2000 which appears to have no name or a space for the name and contains no tables, views or any thing else. However, in Enterprise Manager, it will not allow its deletion or renaming. How do I get rid of the db?

    Thanks,

    Mike W

  • Interesting. How do you know there is a such database there? Can you confirm that by running sp_helpdb and paste the result here?

     

  • I'll have to do that (sp_helpdb&nbsp when I'm back in the client's office tomorrow. In Enterprise Mgr, when listing attached databases, all the databases are shown including the one that has no name. I did do a search of the all the drives and there are no strange mdf or ldf files around.

  • Using Query Analyzer:

    select name,dbid from master.dbo.sysdatabases

    will list the name and database id (dbid) for all the databases on your server. Then using the dbid you can drop the database from the server with:

    drop database QUOTENAME(db_name([dbid]))

    eg: drop database QUOTENAME(db_name(14))


    Julian Kuiters
    juliankuiters.id.au

  • Ran sp_helpdb and it returned list of databases with the first one nothing under name, db_size null, owner administrator, dbid=11, created Nov 20,2003, status=suspect, updateability= read, write,...

    Error msg are 1038, level 15, cannot use empty object or column names.

    Drop database quotename(db_name(dbid)) doesn't work 'cause there is not db_Name and hence generates the error.

    Question: can the db be dropped based in the dbid alone?

  • Can you detatch the db from EM ?

    (rightclick and detatchdb)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Negative on EM detachdb. Returns error mgs #21776 - [SQL-DMO] The name " was not found in the Database collection. If the name is a qualified name, use [] to separate various parts of the name and try again.

    Any more suggestions?

  • You might be able to update sysdatabases table in the master database with a new name and then try dropping it using the new name.

    Good Luck!

  • Already tried altering the master db sysdatabases and it would not allow the changes.

  • Look up -

    system tables, direct updates

    in BOL. This will show you how to alter system tables

     

  • After much trial and error, the command to get rid of the db with no name is as follows:

    Execute sp_detachdb @dbname=N' '

    GO

    Note the space between the single quotes. Thanks for all the comments and help tips.

Viewing 11 posts - 1 through 10 (of 10 total)

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