SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database name is visible but not available.


Database name is visible but not available.

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39886 Visits: 38563
Me thinks that Gail and your Network admin are thinking the same thing here. Sort of looks familar to when our SAN did not come before some of our servers a few times at a previous employer.

Cool
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)
David C Young
David C Young
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 75
Thanks, network admin was looking into how to set a dependency on ISCSI for SQL server.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88385 Visits: 45282
Would this be the database in question?

03/15/2013 07:56:41,spid21s,Unknown,Unable to open the physical file "P:\DB\Prolaw.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
03/15/2013 07:56:41,spid21s,Unknown,Error: 5120<c/> Severity: 16<c/> State: 101.
03/15/2013 07:56:41,spid21s,Unknown,FCB::Open failed: Could not open file P:\DB\Prolaw.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
03/15/2013 07:56:41,spid21s,Unknown,Error: 17204<c/> Severity: 16<c/> State: 1.


If so, at the time SQL starts, the path 'P:\DB' does not exist.

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


kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1612 Visits: 992
If he is a member of the DDL-ADMIN fixed role he could take a database offline (as far as I am aware) and then bring it online.

He wouldn't be able to expand it if he was not mapped to the database at the time of trying to expand the tree or had DB_DENYDATAREADER. At the time he brings the database back online he is automatically mapped which means expanding the tree is no longer a problem.

Only a rough guess that I pulled out of the air.....
kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1612 Visits: 992
If that was the case he wouldn't be able to take the database offline in the first place. He was quite clear that he could see the database (the file exists therefore) and bring it back online (the path is right).

Somehow it reaks a bit of a permissions problem.
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10256 Visits: 4894
kevaburg (3/17/2013)
If that was the case he wouldn't be able to take the database offline in the first place. He was quite clear that he could see the database (the file exists therefore) and bring it back online (the path is right).

Somehow it reaks a bit of a permissions problem.


Do you think that someone is changing the permissions during the reboot? :-)

I would pay close attention to what Gail is stating.

I would not dismiss what the Network Admin is saying. His theory makes sense.

When the Server is restarted can the OP navigate in Windows exlporer to the path of the mdf, ldf?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1612 Visits: 992
I see your point.....

My thought was that if a user did not necessarily have sysadmin rights but still had rights to take a database offline, that the act of bringing it back online would imply an ability to at least browse the structure. The fact that the initial poster said the database was listed but couldn't be expanded until it was taken offline and then brought back online put me in that direction.

Something else that tweaked my interest was the ability to take the database offline and online without an error stating the .mdf wasn't available.

Now I see and understand your train of thought. Could this be resolved by a delayed start of the SQL Server service on a server restart? This is not a problem I have seen before (if this is actually the solution!) so I would be really interested in how it is resolved.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88385 Visits: 45282
kevaburg (3/17/2013)
If he is a member of the DDL-ADMIN fixed role he could take a database offline (as far as I am aware) and then bring it online.

He wouldn't be able to expand it if he was not mapped to the database at the time of trying to expand the tree or had DB_DENYDATAREADER. At the time he brings the database back online he is automatically mapped which means expanding the tree is no longer a problem.


DDL_Admin is a fixed database role, so if he had that, he would have a database user and hence have no problem expanding the DB. There's no automatic creation of database users, certainly not when bringing a DB online. Deny Data reader would not prevent someone from expanding the database tree, just prevent them from seeing the data in the tables/views (and seeing the objects unless they had other permissions).

This isn't a permissions problem. The cause of the problem is listed in the error log posted:

03/15/2013 07:56:41,spid21s,Unknown,Unable to open the physical file "P:\DB\Prolaw.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
03/15/2013 07:56:41,spid21s,Unknown,Error: 5120<c/> Severity: 16<c/> State: 101.


At the time SQL starts, when it initially opens the databases, the mdf is not available. SQL will not retry that access. When the DB is taken offline then brought online, SQL checks again for the file, this time it's there.
This reaks of a delayed availability of a drive, something that would be solved with a dependency or by seeing why the iSCSI is coming online late and fixing it.

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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88385 Visits: 45282
kevaburg (3/17/2013)
My thought was that if a user did not necessarily have sysadmin rights but still had rights to take a database offline, that the act of bringing it back online would imply an ability to at least browse the structure. The fact that the initial poster said the database was listed but couldn't be expanded until it was taken offline and then brought back online put me in that direction.


Anyone who has enough rights to take a DB offline has enough rights to connect to the DB, that's all that's required for the management studio tree.

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


kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1612 Visits: 992
Accepted.....

I withdraw my previously not very well thought out argument!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search