Removing rows from sys.databases when physical file has already been removed

• mbeltz

  Mr or Mrs. 500

  Points: 556

  More actions

  August 23, 2010 at 3:26 pm

  #239314

  I have inherited a sql server 2008 Enterprise edition Server with a small problem.

  Replication was configured and reconfigured several times and at some point the original distribution database was orphaned and now a new database distribution1 is being used for replication. The physical .mdf file has been removed from the server but rows still exist in sys.databases for this DB causing it to show up in management studio and other third party tools as a database stuck in Recovery_pending status.

  So I figured I'd just delete the rows from sys.databases and all would be well. However before I start messing with system tables I thought I'd throw it out to the forum to see if there was a better way or if I was over simplifing the process.

  Thanks in advance.

  mb.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 23, 2010 at 3:50 pm

  #1210723

  You can't delete from the system tables. Besides, sys.databases isn't even a table.

  You shouldn't even be considering editing the system tables unless you're happy with potentially ending up with a corrupt and unusable database (in this case master)

  Drop database?

  Alter database ... set offline followed by a drop?

  Worst case, take a copy of an existing mdf, rename it to match what this DB is expecting (while SQL's not running), start SQL then drop the database.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass