Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,037,796 hits

  • Select GETDATE()

    March 2023
    M T W T F S S
     12345
    6789101112
    13141516171819
    20212223242526
    2728293031  

Posts Tagged ‘setup’

SQL Cluster Setup Error – System.Runtime.InteropServices.COMException (0x80070490): Element not found

Posted by blakhani on May 19, 2015


My job revolves around troubleshooting and fixing the broken thing. Here is one of the situation which I ran into recently and was unable to find solution on internet. It my responsibility to provide self-assist option to the SQL community so that they can find the problem and fix by themselves.

I was trying to install SQL Server 2012 on a 2 nodes Windows cluster. When I tried installing it, it failed with error in subject line. At this first look it sounds like some COM+ error but as always, setup logs are my first place to find the errors. Here is the MSDN link which explains the various files created by setup

https://msdn.microsoft.com/en-us/library/ms143702(v=sql.110).aspx (View and Read SQL Server Setup Log Files)

The information which I saw in setup logs was pretty interesting. In particular, I looked into Detail.txt file which is the parent file of all MSI logs. (I have removed date and time for better reading)

Error: Action "Microsoft.SqlServer.Configuration.SetupExtension.ValidateFeatureSettingsAction" threw an exception during execution.
Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Element not found. (Exception from HRESULT: 0x80070490) —> System.Runtime.InteropServices.COMException (0x80070490): Element not found. (Exception from HRESULT: 0x80070490)
   at Microsoft.SqlServer.Interop.MSClusterLib.ISClusResource.get_Disk()
   at Microsoft.SqlServer.Configuration.Cluster.ClusterPhysicalDisk.get_Partitions()
   at Microsoft.SqlServer.Configuration.ClusterConfiguration.ClusterDiskPublicConfigObject.IsPathOnSharedDisk(String path)
   at Microsoft.SqlServer.Configuration.SetupExtension.SlpInputSettings.ValidateNotOnSharedDisk(ValidationState vs, String directoryName, String bindingKey, String errorMessage)
   at Microsoft.SqlServer.Configuration.SetupExtension.SlpInputSettings.Validate_InstallSharedDir(ValidationState vs)
   at Microsoft.SqlServer.Configuration.SetupExtension.SlpInputSettings.ValidateSettings()
   at Microsoft.SqlServer.Configuration.SetupExtension.ValidateFeatureSettingsAction.ExecuteAction(String actionId)
   at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun)

 

The stack goes from bottom to Top. If we read the function calls made, anyone can conclude that there is something happening with cluster disks. That’s a good hint. So, I went back to failover cluster manager and looked into Disks under “Storage”. There was a disk which was in failed state. I was not able to bring this online and that’s THE problem! SQL Setup would enumerate the disks to find eligible disks which can be used and it’s not able to find details about that disk. Here was the error when I attempted to bring it online.

The resource ‘Cluster Disk 1’ did not come online.
The desired state change for ‘Cluster Disk 1’ did not occur before the timeout expired.

I realized that I have played with iSCSI and messed up the disk which was presented.

Solution: Delete the disks which are not able to come online under “Storage > Disks” or “Available Storage” in failover cluster manager interface.

Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle
    • Advertisement

    Posted in Cluster, Installation, Setup, SQL Server 2012, SQL Server 2014 | Tagged: , , , , | 3 Comments »

    Solution : Cannot add new node – Rule "SQL Server Database Services feature state" failed.

    Posted by blakhani on September 11, 2014


    While deploying SQL Server 2014 cluster in my lab I ran into this problem when I was trying to add second node (Node 2) to SQL cluster.

    —————————
    Rule Check Result
    —————————
    Rule "SQL Server Database Services feature state" failed. The SQL Server Database Services feature failed when it was initially installed. The feature must be removed before the current scenario can proceed.
    —————————
    OK
    —————————

    Above error means that when I installed SQL Server on first cluster node, it didn’t install properly. There were some errors while creating cluster and due to that AddNode is blocked. In my case also this is true because I had an issue with SQL Server Network Name Resource and it didn’t come online earlier. Below was the original error on Node 1

    Cluster network name resource ‘SQL Network Name (Balmukund)’ cannot be brought online. The computer object associated with the resource could not be updated in domain ‘MyDomain.com’ for the following reason:
    Unable to update password for computer account.

    The text for the associated error code is: Access is denied.

    The cluster identity ‘WinCluster$’ may lack permissions required to update the object. Please work with your domain administrator to ensure that the cluster identity can update computer objects in the domain.

    I fixed above error by giving proper permission to cluster computer object on Balmukund (which was the network name of SQL) After fixing that I created SQL Server and SQL Agent manually. Sometime it may also happen that SQL Agent didn’t come online on Node 1 due to some failure and it was fixed after setup was completed.

    So, if we see above rule failure then we should go to the first node and check setup log files to see if the installation succeeded or failed. If the first node installation had some failures which were fixed later then we should do a repair of the installation. Repair option can be found under the "Maintenance" page in SQL Server Installation Center (setup).  This action will clear the failure state and allow the node addition action.

    If you are lazy like me to do repair then you can also check registry key
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\ConfigurationState

    If you see value as 2 for any component then there were some failures of those components. In most of the cases since issue would be with SQL Server or SQL Agent, we might see 2 for MPT_AGENT_CORE_CNI, SQL_Engine_Core_Inst, SQL_FullText_Adv, SQL_Replication_Core_Inst

    Note: that we MUST fix the error on first node before taking shortcut of registry value. Once error is fixed, we can make the value of those component to 1 (means success)

    The highlighted value MSSQL12.SQL2014 might vary based on SQL version and Instance name. The first piece for SQL 2008 it would be MSSQL10, for SQL 2008 R2 it would be MSSQL10_50, for SQL 2012 it would be MSSQL11 and for SQL 2014 it would be MSSQL12. Second piece is instance name (for me instance name is SQL2014)

    Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in Installation, Troubleshooting | Tagged: , , , | 2 Comments »

    Information : Why we can’t use system database files from “Template” folder rather than rebuilding system databases

    Posted by blakhani on February 27, 2014


    In my recent conversation on SQL Bangalore User Group page, I had interesting discussion about rebuilding system database in SQL Server 2008. One of the community member mentioned that we can take the master database files from below location

    C:\Program Files\Microsoft SQL Server\<InstanceName>\MSSQL\Binn\Templates

    and keep them to the current location of master.

    I have explained that why above will not work but there is so much a write about it. So, thought of writing a blog post (for better search because Facebook comments are not shown as result by search engines)

    Let’s first understand why we keep those file in that location. In earlier version of SQL (2000 and 2005) if there is a need to rebuild system databases, we need to get the installation media (DVD or network share). In SQL 2008 onwards, we don’t need DVD or media to rebuild system databases. During installation of SQL, setup does the caching of setup.exe (and other files needed for setup) and also the MDF and LDF file in Template folder. In this directory you will find master, model, and msdb database and log files that were copied from your installation source as part of setup. The path of setup.exe for SQL 2012 is “C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012”. You can easily locate similar folder for SQL 2008 or R2 as well.

    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\Templates

    So the logic in rebuilding the system databases is to get these files and copy them into your DATA directory to obtain your new system databases. Now, you may ask, what would happen if I just copy those files from Templates to data folder? I’ll explain then in a bit. Setup does few more things other than copy paste.

    image

    If we rebuild the system databases, setup would move the files from above location to the proper location and (this is important) it modifies the path of other databases which is stored in master database. When SQL was packaged, we didn’t know you are going to use F drive (or any other drive) to keep system databases so the path is from a machine where SQL product was packaged.

    Let’s see what would happen if I just move the files and start SQL Server. Well, you might have guessed it, we won’t be able to start SQL. Notice the path and filename which I highlighted below.

    2013-12-06 16:00:51.61 spid9s      Starting up database ‘mssqlsystemresource’.
    2013-12-06 16:00:51.62 spid5s      Error: 17204, Severity: 16, State: 1.
    2013-12-06 16:00:51.62 spid5s      FCB::Open failed: Could not open file e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBData.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
    2013-12-06 16:00:51.62 spid5s      Error: 5120, Severity: 16, State: 101.
    2013-12-06 16:00:51.62 spid5s      Unable to open the physical file "e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBData.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
    2013-12-06 16:00:51.62 spid5s      Error: 17207, Severity: 16, State: 1.
    2013-12-06 16:00:51.62 spid5s      FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf’. Diagnose and correct the operating system error, and retry the operation.
    2013-12-06 16:00:51.62 spid5s      File activation failure. The physical file name "e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf" may be incorrect.
    2013-12-06 16:00:51.67 spid9s      The resource database build version is 11.00.3000. This is an informational message only. No user action is required.
    2013-12-06 16:00:51.98 spid9s      Starting up database ‘model’.
    2013-12-06 16:00:51.98 spid9s      Error: 17204, Severity: 16, State: 1.
    2013-12-06 16:00:51.98 spid9s      FCB::Open failed: Could not open file e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\model.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
    2013-12-06 16:00:51.98 spid9s      Error: 5120, Severity: 16, State: 101.
    2013-12-06 16:00:51.98 spid9s      Unable to open the physical file "e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\model.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
    2013-12-06 16:00:51.99 spid9s      Error: 17207, Severity: 16, State: 1.
    2013-12-06 16:00:51.99 spid9s      FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\modellog.ldf’. Diagnose and correct the operating system error, and retry the operation.
    2013-12-06 16:00:51.99 spid9s      File activation failure. The physical file name "e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\modellog.ldf" may be incorrect.
    2013-12-06 16:00:52.02 spid9s      Error: 945, Severity: 14, State: 2.
    2013-12-06 16:00:52.02 spid9s      Database ‘model’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

    This is one of the error which you might also see when SQL installation is not complete and then SQL Service not getting started. People have posted on various forums about this problem. The root cause of the issue was that SQL Setup couldn’t alter the location for other system databases and hence looking for some weird path. If its SQL 2008 you might see path as e:\sql10_main_t\sql\mkmastr\databases\objfre\i386\

    Here is the information taken from SQL Installation logs which confirms our theory. They are from my own machine

    • Files getting copied

    (01) 2013-07-02 10:24:27 SQLEngine: : Installing system database files
    (01) 2013-07-02 10:24:27 Slp: Sco: File ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf’ does not exist
    (01) 2013-07-02 10:24:27 SQLEngine: : Copying database file ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates\master.mdf’ to ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf’
    (01) 2013-07-02 10:24:27 Slp: Sco: Attempting to copy file ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates\master.mdf’ to file ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf’
    (01) 2013-07-02 10:24:27 Slp: Sco: Attempting to get security descriptor for file ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates\master.mdf’
    (01) 2013-07-02 10:24:27 Slp: Sco: Returning security descriptor O:SYG:SYD:AI(A;ID;FA;;;SY)(A;ID;FA;;;BA)(A;ID;0x1200a9;;;BU)(A;ID;0x1200a9;;;S-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003)

    • Same operation done for mastlog.ldf, model.mdf, modellog.ldf, MSDBData.mdf and MSDBLog.ldf as well.
    • Then SQL is started with various trace flags, parameters as shown below.

    (01) 2013-07-02 10:28:08 SQLEngine: –SqlServerServiceSCM: Starting SQL via SCM (-mSqlSetup -Q -qSQL_Latin1_General_CP1_CI_AS -T4022 -T4010 -T3659 -T3610 -T8015)…

    • A connection is made and the various scripts would be executed to modify the information in files picked from template folder.

    (01) 2013-07-02 10:28:19 SQLEngine: –SqlDatabaseServiceConfig: Connection String: Data Source=\\.\pipe\SQLLocal\MSSQLSERVER;Initial Catalog=master;Integrated Security=True;Pooling=False;Connect Timeout=300;Network Library=dbnmpntw;Application Name=SqlSetup
    (01) 2013-07-02 10:28:19 SQLEngine: : Checking Engine checkpoint ‘ServiceConfigConnect’
    (01) 2013-07-02 10:28:19 SQLEngine: –SqlDatabaseServiceConfig: Connecting to SQL….
    (01) 2013-07-02 10:28:19 Slp: Sco: Attempting to connect script
    (01) 2013-07-02 10:28:19 Slp: Connection string: Data Source=\\.\pipe\SQLLocal\MSSQLSERVER;Initial Catalog=master;Integrated Security=True;Pooling=False;Connect Timeout=300;Network Library=dbnmpntw;Application Name=SqlSetup
    (01) 2013-07-02 10:28:19 Slp: Connected successfully…
    (01) 2013-07-02 10:28:19 SQLEngine: –SqlDatabaseServiceConfig: Running queries SQL….
    (01) 2013-07-02 10:28:19 SQLEngine: : Install script parameters:
    (01) 2013-07-02 10:28:19 SQLEngine: :  Instance Name: MSSQLSERVER
    (01) 2013-07-02 10:28:19 SQLEngine: :  Sql Collation: SQL_Latin1_General_CP1_CI_AS
    (01) 2013-07-02 10:28:19 SQLEngine: :  System Data Dir: Microsoft.SqlServer.Configuration.Sco.SqlDirectory
    (01) 2013-07-02 10:28:19 SQLEngine: :  Tempdb Dir: Microsoft.SqlServer.Configuration.Sco.SqlDirectory
    (01) 2013-07-02 10:28:19 SQLEngine: :  Tempdb Log Dir: Microsoft.SqlServer.Configuration.Sco.SqlDirectory
    …

     

    To conclude, you should not use system database files from “Templates” folder as it is by using copy paste method. You must use /ACTION=REBUILDDATABASE

    Hope this would explain a little about usage of template folder.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in SQL Server User Group, SQLBangUG | Tagged: , , , , , | 8 Comments »