Orphaned DB?

• dkschill

  Hall of Fame

  Points: 3375

  More actions

  April 12, 2013 at 1:33 pm

  #273026

  So I am exploring PowerShell, and found a DB with .Databases that I can't see in sys.databases.

  I ran this following (starting from the Machine level with sqlps):

  $i = ls

  $i | %{$_.Databases}

  It returns a list of all the DBs, but there is one that says that the state is Normal, Standyby.

  I don't see it through SSMS, and it isn't in sys.databases.

  What could cause this and what should I do about that?

• Lowell

  SSC Guru

  Points: 323504

  More actions

  April 12, 2013 at 1:35 pm

  #1606132

  check the same database in SSMS: is it set as offline? a lot of processes don't see offline databases, i know i've tripped over that a few times.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• dkschill

  Hall of Fame

  Points: 3375

  More actions

  April 12, 2013 at 1:37 pm

  #1606137

  Unfortunately it doesn't show up at all in SSMS.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 12, 2013 at 1:42 pm

  #1606142

  dkschill (4/12/2013)

  ---

  So I am exploring PowerShell, and found a DB with .Databases that I can't see in sys.databases.

  I ran this following (starting from the Machine level with sqlps):

  $i = ls

  $i | %{$_.Databases}

  It returns a list of all the DBs, but there is one that says that the state is Normal, Standyby.

  I don't see it through SSMS, and it isn't in sys.databases.

  What could cause this and what should I do about that?

  What is the name of the databse?

• dkschill

  Hall of Fame

  Points: 3375

  More actions

  April 12, 2013 at 1:44 pm

  #1606143

  It is RISDB, and I was able to look up some of the metadata with Powershell. I can see that it was created last year by one of our DBAs.

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  April 13, 2013 at 5:40 am

  #1606241

  Are you sure you're comparing to the correct instance?

  When I run this PoSh code:

  Add-PSSnapin SqlServerProviderSnapin100

  Add-PSSnapin SqlServerCmdletSnapin100

  Set-Location SQLSERVER:\SQL\orlando\sql2008r2$i = ls

  $i | %{$_.Databases}

  This is what is run on the instance when the ls is run:

  exec sp_executesql N'SELECT

  dtb.collation_name AS [Collation],

  dtb.name AS [DatabaseName2]

  FROM

  master.sys.databases AS dtb

  WHERE

  (dtb.name=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'master'

  exec sp_executesql N'SELECT

  dtb.name AS [Name]

  FROM

  master.sys.databases AS dtb

  WHERE

  (dtb.name=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'msdb'

  exec sp_executesql N'SELECT

  dtb.collation_name AS [Collation],

  dtb.name AS [DatabaseName2]

  FROM

  master.sys.databases AS dtb

  WHERE

  (dtb.name=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'msdb'

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• dkschill

  Hall of Fame

  Points: 3375

  More actions

  April 13, 2013 at 11:30 am

  #1606256

  I am 95% sure. All the other databases are the same except the one. I will run those scripts that you caught in a trace...and if I still don't see the DB then I will run a trace and see if something is a little different. I am launching sqlps from powershell...not sure on version...I know that I have put 2008 R2 on the machine I am running the scripts from.

  Thanks again for the help,

  Dane