March 17, 2013 at 7:54 am
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/
March 17, 2013 at 9:07 am
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.
March 17, 2013 at 12:42 pm
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
March 17, 2013 at 12:46 pm
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
March 17, 2013 at 12:59 pm
Accepted.....
I withdraw my previously not very well thought out argument!
March 18, 2013 at 7:52 am
I would still check the space available for database and logs and if there are any other resource bottleneck in the database/server in the case the database is the only biggest in the database server.
March 18, 2013 at 8:39 am
Thanks everyone for all the input.
Looks like ISCSI isn't getting the link to the DB drive set fast enough. Some other research recommends setting a delay on the SQL Server service of just a couple of seconds as being the best way to resolve. We'll be testing that some night this week.
March 18, 2013 at 11:11 am
At the following URL:
http://serverfault.com/questions/291469/sql-server-wont-start-due-to-luns-being-unavailable
I found this:
Are you using the Microsoft iSCSI software initiator? Are the defined iSCSI drives dynamic?
There is a known issue where the software iSCSI initiator doesn't start dynamic drives properly on boot. My work-around was to redefine the drive as a "basic" drive instead. Of course, changing this means you will lose all the data on the drive and have to reformat.
This site has a work-around which may be more useful.
share|improve this answer answered Jul 18 '11 at 16:17
David Mackintosh
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/
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply