Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can't see DBs content after server reboot


Can't see DBs content after server reboot

Author
Message
arrnvid
arrnvid
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 63
Hello All
Got an issue on SQL Server 2005.
Everytime server (SBS2003 - Host for SQL 2005) is rebooted and I conn to SSMS, I cannot see contant of the DBs.
I got 2 DBs created and I can see them under Object Explorer but once small + sign is pressed nothing is showing up.
Once I stop and start DB engine, from the top of the tree all works fine.

Any idea for fix, I would really appreciate?
So far checked all SQL Services - all working fine and started fine
Thanks in advance
Kris
GilaMonster
GilaMonster
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55265 Visits: 44699
My guess is that the SQL service is starting before the disks are available. If you check the error log, you'll probably find a lot of errors relating to SQL unable to find/access the data files.
Check the error logs, see if that is the case.

What IO subsystem are you running on?

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


arrnvid
arrnvid
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 63
Thank you very much for the support

I/O used:
- EMC AX4-5F Fibre SAN
- QLogic HBA 4GB/s

Checked the logs and found something similar to "can't locate the file":
***************
2009-03-27 19:04:03.09 spid13s Unable to open the physical file "E:\SQL Data\Data.mdf". Operating system error 3: "3(error not found)".
***************
Note:
E:\ - hosts DB data
F:\ - hosts DB logs
both on the starage
I will try to dealy SQL Service by few min (3min) and see if that will help
Any other things I can try?

Regards
Kris
Steve-3_5_7_9
Steve-3_5_7_9
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 1590
...Wait
You also need to wait for the databases to recover, after a restart. Transactions may need to be recovered/completed. I've had to wait upwards of 10 minutes for a clustered server to recover when it's group was moved to another node.

steve



arrnvid
arrnvid
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 63
Thank you very much Steve ... your comment made me loook closer into the configuration
This particular configuration is on the clustered mode...
So if you recommend to wait, can you tell me if DBs will reatach automaticaly after specific period of time (~10min)?
At the moment we have to manualy reatache conn to DBs via SSMS.
Is the a way of checking DBs status automaticaly? (whether it is accesable or not via Batch or stored procedure)

Thank you once again for the help
Regards
Kris
MarkusB
MarkusB
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4683 Visits: 4208
Sounds like the dependencies between the cluster resources is not configured correctly.
In the Windows Cluster Admin tool make sure that the SQL Server service depends on both disks. This way you make sure that the SQL server won't start until both disks are online.

Markus Bohse
Steve-3_5_7_9
Steve-3_5_7_9
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 1590
Our cluster has these dependancies, but you still need to wait for the master to recover before you can access any of the other databases, and then you have to wait for the other databases to recover any transactions. The SQL Server log shows this recovery for each database coming on-line and recoverying "in doubt" transactions.



EllieDBA
EllieDBA
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 132
Steve,
Not to cast any shadow on the main post, can you give us some incentive on how to reboot cluster environment for SQL risk free? I understand there are conflict opinions about how to go about the process. If there is a cluster with 2 nodes, where do we start form? Maybe the original post would benefit from this as well.

thank you for any input,
Steve-3_5_7_9
Steve-3_5_7_9
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 1590
Risk Free?

Since the primary purpose of the cluster is for high availability with regards to hardware failures, uncommitted transactions would be rolled back; so I would do the following for a "planned" reboot of a cluster node.

1. Have the applications come off-line, or restricted. I would even set the database to restricted_user so "user" connections would not be made. Doing this would prevent "uncommited" transactions from being rolled back.
1a. Back up the transaction log, so a "checkpoint" occurs. If I have to kill users, I would perform this backup first, so the work would be commited.
2. I would move the cluster group over to the opposite node of the one that is going to be rebooted.
3. Once the group came back "online", I would reboot the passive node.
4. If I needed to reboot the "active" node, I would wait for the previously rebooted node to come "online", then move the active groups over to the other node.
5. Once the groups came back "online", I would then reboot the passive node.
6. After this node came back "online", I would then move the group to the desired node.
7. Unrestrict the databases and have the applications become available.


steve



arrnvid
arrnvid
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 63
Hello All

Just for clarification for the current environment state. It it a bit complicated, I hope I will make myself clear:
I got a clustered environment:
2 nods:
- NODA - Comp1 (Physical box) with access to the storage (LUN)
- NODB - Comp2 (On VM - W2K3) with access to the same LUN
SQL Server is hosted on the Comp1;
DB and Trans Log are on the LUN (on the storage)
Drive E:\ hosts .mdf file
Drive F:\ host .log file

(If you want to ask why it is configured like that - honestly, do not have a clue)
btw. this is a production environment.
ok, the next server reboot is schedule for Friday after Server patching.
I have made SQL Services depend on Cluster Services. (Cluster Services should make all necessary connection to the LUN)
As long as we got connection established to E:\ and F:\ Drive, SQL should have connectivity to DBs.

If you guys have any suggestions, please let me know.
I will post all results after Friday's reboot.

Regs
Kris
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