We help IT Professionals succeed at work.

SQL Cluster Failed lost Primay SQL instance

96 Views
Last Modified: 2019-02-28
SQL 2014 Cluster 2 Nodes 3 instances
Windows 2012 R2
VMware ESXI 6.5
iSCSI drives

Today one of my iSCSI drives filled up.  I went onto SQL and tried to shrink a large data base trans log file it failed to do so I I tried to add another drive to the VM and I lost my  entire "E" drive on the 'E" drive is the install of the SQL 2014 server.   and the data bases.  I have backup drive of all databases and they are copied off to NAS drive daily.
My databases are ok

This instance was the first instance I installed and now it is gone.
I can get to the other instances but not the primary one.

The drives are shared between the nodes using iscsi and cluster manager.


I now have my E Drive back but it is empty.  I need to get the instance back.

So I tried to install SQL 2014 again  

I get to Cluster Node Configuration and it fails "There is no SQL Server failover cluster available to join.


Am I hosed?  Do I need to reinstall SQL from scratch?

Need assistance

Thank you

tom
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
>> I now have my E Drive back but it is empty.
>> There is no SQL Server failover cluster available to join

If you don't have a DR node available, then you might need to reconfigure Cluster(as you mentioned you couldn't identify the cluster at all) and then SQL from the scratch and then restore all the database backups you have..

This is one scenario where you can learn a lot:
1. Keep Transaction Log file sizes in control by taking Log backups regularly.
2. Monitor the free space available in Drives regularly and act on it immediately.
3. Perform Capacity planning well in advance so that your drives will not be used completely causing these kind of issues.
Thomas GrassiSystems Administrator

Author

Commented:
Hi I got it back up by doing this command

cd \program files\Microsoft sql server\120\setup bootstrap\sqlserver2014
setup /q /action=rebuilddatabase /instancename=mssqlserver /sqlsysadminaccounts=sa /sapwd=youreyesonly99

The master default and model etc databases where created.

I was able to start the mssqlserver service and open in SSMS

I stopped the mssqlserver service  and ran this net start mssqlserver /m    places sql in single user mode so I can restore the master database

from an administrator cmd prompt I try this

sqlcmd  -S  TGCS014-N1-SQLC -E    hit enter get 1> prompt
1> RESTORE DATABASE master FROM DISK = 'F:\path\master.bak' WITH REPLACE
2>  GO

Now off to restore the other databases


My problem is I can not restore the databases now

Error

System.data.sqlclient.sqlerror cannot use file e:\sql-data\database.mdf for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.  Either the disk respource containing the file in not present in the cluster group or the cluster resource of the sql server does not have a dependency on it   Microsoft.sqlserver.smoextended

Any ideas?  

Thanks
Thomas GrassiSystems Administrator

Author

Commented:
All my user databases are in Recovery Pending

I do not have any mdf or ldf files  

I just have .bak files

I tried just restore database mydb

The restore statement is invalid in the current context The recover data only option is only defined for secondary filegroups when the dtabase is in an online state. when the database is in an offline


How can I restore my databases

Urgently needed
Thomas GrassiSystems Administrator

Author

Commented:
I think I know why I can not restore

The cluster roles has a failed status.

That's because SQLAGENT service is not running.


After restoring the master database and then the MSDB i should have been able to start the SQLAGENT service but it will not start.

Any ideas?


Please help
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
>> The cluster roles has a failed status.
>> After restoring the master database and then the MSDB i should have been able to start the SQLAGENT service but it will not start.

Can you kindly check what is the error message you are receiving in Cluster and SQL Server Agent error log as that might help resolve the issue.
First, I would suggest you to see whether your SQL Server and SQL Server Agent Service are added and available in Cluster service properly or not.
If not, then I would suggest you to get the Cluster issues fixed out completely by ensuring all nodes, disks or other resources are listed properly..
Once this is done, then we can check the SQL Server Agent Error Logs in SQL Server installation logs path to see why SQL Server agent service is not starting up..
Thomas GrassiSystems Administrator

Author

Commented:
Only messages I see in the sql logs are users attempting to connect to the user database.  all fails since database is not there.

No sql agent logs since sql agent is not running

From services I try to start sqlagent it starts and stops right away

On SSMS  it shows    SQL SERVER AGENT (AGENT XP's disabled)   how do i enable that right click on it and the menus is mostly greyed out

Once I get SQL Agent started I think things will be good.

I removed SQLagent from the cluster and now the  SQL Cluster role is running

Also on SSMS the SQL Server ICON is WHITE NOT GREEEN  

Is there a sqlcmd to restore my user databases an example please.

I tried and it tell me that you can only restore master database in single user mode.

I am not running in single user mode  is there a way to check that too?


Sorry for so many questions

Thank you,

System is still down
Thomas GrassiSystems Administrator

Author

Commented:
Still having major issues

SQL Agent will not start that's first problem

I ran this

sp_configure 'show advanced options',1

go

reconfigure with override

go
 
sp_configure 'Agent XPs',1

go

reconfigure with override

go
 
sp_configure 'show advanced options',0

go

reconfigure with override

go


No change still ca not start sqlagent service


The databases are still unable to be restored but if we get sqlagent working then we can tackle the database issues.


Help needed
Thomas GrassiSystems Administrator

Author

Commented:
Guys Update.

I got sql agent running it was a permissions problem on the log folder

NOW MY ONLY PROBLEM is I can not restore any user database.

sql restore error

Looking for assistance


Thank you

Tom
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Thomas GrassiSystems Administrator

Author

Commented:
Raja

NP  the article is very old

All disks including the failed E Drive are configured as dependences

I ran a dependency report everything looks good

I am trying to add the SQL agent now but it fails the role.

I think it is because  from the Microsoft SQL Server Management Studio   I see white icons by The SQL Server name and the SQL Server agent
they should be green


Server White not green  

The services are started why they white?  should be green  my other instances are green

Any ideas.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
>> I see white icons by The SQL Server name and the SQL Server agent they should be green

If it is green then it indicates that your current account have privileges to see SQL Server service status, if white your account doesn't have privileges to see the Service status but the Service is running so not a problem.

In the meanwhile, why all databases are still in Recovery pending status, how long are they in this status..
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
>> I am trying to add the SQL agent now but it fails the role.

SQL Server Agent Service is dependent on SQL Server service as well, kindly ensure that dependency is configured in cluster or not..
Thomas GrassiSystems Administrator

Author

Commented:
Raja

Yes I understand that the SQL agent has to be part of the cluster but it will not let me add it.  when I do it stops the role on the cluster.

Need to understand why I can not add the sql agent to the cluster.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Thomas GrassiSystems Administrator

Author

Commented:
Rega.

I figured out why when I added the sql agent to the cluster I needed to go into the properties and set the name of the sql server and check the other settings now all three instances are online.

My final issue is to restore the USER databases.

What is a good procedure for doing that?

Every thing I tried falls

Thank you
Thomas GrassiSystems Administrator

Author

Commented:
RAJA

I got it back up and running now.

I had to add the new disk to the dependency on the cluster role.

Thanks for all your help
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
>> I had to add the new disk to the dependency on the cluster role.

Glad to hear you had it resolved..
Yes, this is what I was referring to earlier..
Add appropriate dependencies for SQL Server, SQL Server Agent Service and all the clustered volumes(your E: drive which went down) to get it working..
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.