Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Orphaned DB? Expand / Collapse
Author
Message
Posted Friday, April 12, 2013 1:33 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
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?
Post #1441890
Posted Friday, April 12, 2013 1:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1441891
Posted Friday, April 12, 2013 1:37 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
Unfortunately it doesn't show up at all in SSMS.
Post #1441896
Posted Friday, April 12, 2013 1:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 23,397, Visits: 32,243
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441901
Posted Friday, April 12, 2013 1:44 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
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.
Post #1441902
Posted Saturday, April 13, 2013 5:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1442026
Posted Saturday, April 13, 2013 11:30 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
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
Post #1442045
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse