Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Wednesday, November 25, 2015 6:33 AM
Points: 459, Visits: 430
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 @ 1:10 PM
Points: 14,412, Visits: 37,721
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!
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: Wednesday, November 25, 2015 6:33 AM
Points: 459, Visits: 430
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: Yesterday @ 6:53 PM
Points: 23,123, Visits: 36,842
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: Wednesday, November 25, 2015 6:33 AM
Points: 459, Visits: 430
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: Friday, June 24, 2016 4:38 PM
Points: 7,917, Visits: 14,266
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: Wednesday, November 25, 2015 6:33 AM
Points: 459, Visits: 430
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