Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can't see DBs content after server reboot Expand / Collapse
Author
Message
Posted Monday, March 30, 2009 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 24, 2013 3:53 AM
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
Post #686043
Posted Monday, March 30, 2009 7:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #686056
Posted Monday, March 30, 2009 7:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 24, 2013 3:53 AM
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
Post #686111
Posted Tuesday, March 31, 2009 6:22 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 5:58 AM
Points: 924, Visits: 1,445
...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



Post #686933
Posted Tuesday, March 31, 2009 6:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 24, 2013 3:53 AM
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
Post #686959
Posted Tuesday, March 31, 2009 7:07 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:31 AM
Points: 4,429, Visits: 4,183
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
Post #686976
Posted Tuesday, March 31, 2009 7:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 5:58 AM
Points: 924, Visits: 1,445
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.


Post #686986
Posted Tuesday, March 31, 2009 9:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 4, 2012 9:40 AM
Points: 54, 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,

Post #687129
Posted Tuesday, March 31, 2009 11:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 5:58 AM
Points: 924, Visits: 1,445
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



Post #687276
Posted Wednesday, April 1, 2009 9:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 24, 2013 3:53 AM
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
Post #688049
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse