March 30, 2009 at 6:55 am
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
March 30, 2009 at 7:09 am
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
March 30, 2009 at 7:55 am
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
March 31, 2009 at 6:22 am
...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
March 31, 2009 at 6:49 am
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
March 31, 2009 at 7:07 am
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.
[font="Verdana"]Markus Bohse[/font]
March 31, 2009 at 7:13 am
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.
March 31, 2009 at 9:01 am
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,
March 31, 2009 at 11:32 am
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
April 1, 2009 at 9:09 am
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
April 9, 2009 at 3:04 am
Hello All
As promised, results after server reboot.
Firstly, I would like to thank for all the responses. All were very helpful.
Secondly, quick update. Looks like issue is resolved.
After I took Your advice, I've made SQL Service depend on Cluster Services.
Done that via Regedit as the is no facility to add dependencies in the Console.
I have added "DependOnService" Mulit-String Value and Modified with value: ClusSvc
I have done it for all SQL Instances installed.
After closing Regedit, checked MSSQLServer Services under Services.msc and all SQL Dependencies were updated.
After Server reboot all DBs were mounted correctly, with no need to mount them manually.
All works fine now.
Thank you once again for all advice.
April 10, 2009 at 10:04 am
Thank you for updating us. Do you think give us some incentive as step by step (your best practice that you took) the restart and reboot process for a cluster with two nodes?
Would really appreciate it,
Ellie
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply