Unable to Open SQL Server Database Properties Window as it Returns Error

SQLServerF1

SQL Server Database Property page can be used to view or modify options for the database like changing the Collation, Recovery model, Compatibility level, Auto Close, Auto Create Statistics, Auto Shrink, etc. Sometimes you may receive below mentioned error when you right-click on the database and click on properties option.

Cannot show requested dialog. (SqlMgmt)
Property Owner is not available for Database ‘TestDB’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

This issue may not happen with all databases, as you may be able to open other databases properties on same SQL Server instance without any errors.

The reasons why this error may appear could be that there is no owner for this database either because the login has been deleted from the SQL Server or if it is a windows or domain account then the account may have been removed from the AD. Also it may be possible that SQL Server cannot validate this account due to permission or connectivity issues with AD.

As you are not able to use the GUI to check the database properties, so we cannot see if there is Database owner present for the database and if present then what is the name of that account. Other options to verify the owner of a database is by running the below query.

sp_helpdb [DBName]

Once you identify that there is no database owner or the login does not exist any more, you can run the below command to change the database owner to a different account.

Use [DBName]
GO
EXEC sp_changedbowner ‘SA’;

After running the above command you can Now you can now open the properties of the database without any error. Instead of ‘SA’ account you can choose any login which exists in the SQL Server instance and is valid, However it is important to note that after sp_changedbowner is executed, the new owner is known as the dbo user inside the database. The dbo has implied permissions to perform all activities in the database. Meaning the database owner can perform all operations inside the database inclusing dropping the database itself.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Installation Wizard disappears After Setup Support Files

SQLServerF1

As a DBA it is common to perform installation of SQL Server and related components. Most of the times, the SQL Server installation progressed soothly. But sometimes when the SQL Server install fails it may get very difficult to identify the cause and then to find a solution.

One of the strange or weird issue one may face with the SQL Server instance is that when you start the SQL Server installation, you will be prompted to install Setup support file after that the Setup wizard provides further options to choose the instance name, location for data, log file and service accounts, etc. However you may notice that after the installation of setup support files, the setup window just disappears and does not show up again to proceed further with selecting other options.

Generally SQL Server setup logs are created under C:\program files\Microsoft SQL Server\100(different for each SQL version)\Setup Bootstrap\Log\ folder. But if the installation wizard disappears or fails after installation of Setup Support files or before that the the install log relate to this will be located at temp folder. You can get to this location by following below instructions.

Click on start->run and type %temp% and press enter (basically, go to the temp folder)
Here, look for SQLSetup.log and SQLSetup_1.log. Open the SQLSetup_1.log file.

Check these logs to see if you can find any specific errors or warnings. If you are unable to understand anything from those log files then you can do a quick search based on the error or warning messages in that log.

Coming back to the original issue of the SQL Server installation window getting disappeared after going through few installation wizard screens, you may try below steps to see if it resolves the issue.

– Open Control Panel – Add or Remove Programs and uninstall SQL Server Setup Support files. There may be multiple setup support files of multiple SQL Server instances, so remove them all.

– Logoff from the server, if possible reboot the server.

– Log back into the server and then start the SQL Server installation and now it should proceed further with the installation and should show the other options.

If above solution did not solve your problem with the SQL Server installation, then you can try the option of slip-streaming the SQL Server installation with latest Service Pack and then attempt the installation.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Installation Failure due to Cabinet File (SQL.CAB) Error

SQLServerF1

As a DBA it is common to install SQL Server and related components. Most often or not the SQL Server installation is easy and completes without any errors. But sometimes when SQL Server installation fails, it makes it very difficult to identify the problem and to find a solution for the same.

There are various reasons why SQL Server installation fails and below is one such error which you may receive while trying to install SQL Server instance using the setup media from DVD mounted drive.

The following error has occured:
The cabinet file ‘Sql.cab’ required for this installation is corrupted and cannot be used. This could indicate a network error, an error reading from the CD-ROM, or a problem with this package.
Click ‘Retry’ to retry the failed action, or click ‘Cancel’ to cancel this action and continue setup.

Looking at the above error, first thing which can think about is that this error has something related to the corrupt media and some problem with the installation media. So, we can try below steps to see if we can get around the error.

– Launch the Setup by choosing “Run as Administrator”

– Copy the SQL Server Installation media to local disk instead of DVD drive or Network.

– Make sure the Setup media is not corrupt, one way to verify this is to try and install the SQL Server instance on another test server to see if the issue still persists.

– Try downloading the media again from the MSDN site using download manager.

– Extract the contents of SQL Server Media ISO file using MagicISO and then try to install from the extract media.

– Turn off or Uninstall any Anti-Virus software running on the system. Reboot of the server is required after making this change.

– Sometimes this error may be returned of the windows installer file is corrupt, so if above did not work, then it is good option to try and uninstall the Windows installer and reinstall it. Reboot the server and then attempt to install again.

Hopefully one of the above solution should help you in resolving this issue.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Snapshot Agent Fails while Generating Snapshot in SQL Server 2014

SQLServerF1

Replication in SQL Server is most widely used and most often of not the setup and configuration of Replication is quite simple. However there are many issues with the replication which causes problems for DBAs and with each release of newer versions new problems come up.

On such problem with replication comes with setting up replication on SQL Server 2014 instance. When you setup Merge replication and then try to initialize the replication by running the Snapshot agent, it may fail with below error.

There is already an object named “TestConstraint” in the database. Could not create constraint or index. See previous errors.

There is no special configuration with the replication, same database, same tables and same data on SQL Server 2008 R2 or 2012 instance works fine without any errors, this happens only with SQL Server 2014.

After looking at the code where it is failing, we noticed that it is trying to create system table MSmerge_conflict_publication_article where it fails. After digging and research found that in SQL Server 2014 the Snapshot Agent is trying to generate the create script to create the table MSmerge_conflict_publication_article with columns, it is also defining a constraint with same constraint name which already exists in the table as part of user table constraint. Here both the user table constraint name and system table constraint name are same thus causing this error.

Because of this change in SQL Server 2014, the merge replication can fail on tables which have constraints defined on them. This appear to be bug, but not seem to have any solution so far. Post talks about same problem and they have already tested installing SQL Server 2014 CU4, but the issue still persisted.

As of now one of the workaround is to drop all constraints before on the user tables which are replication and create them later. Other options which can be tried include initializing the Subscriber database using other methods such as Backup/Restore where we can avoid the Snapshot agent from running the scripts. Hopefully this issue will be fixed soon.

This is applicable on below versions of SQL Server

SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server 2012 Setup Failure due to Failure of InstallFeatureSpecificRules

SQLServerF1

Problem with installing SQL Server 2012 as it fails while checking some rules related Install Feature Specific Rules. Below is the error that occurred when the rule fails.

InstallFeatureSpecificRules: SQL Server 2014 Setup configuration checks for rules group ‘InstallFeatureSpecificRules’
RS_IsDotNet3_5Installed This rule determines if the Microsoft .NET Framework 3.5 Service Pack 1 is required on the system, based on the features you selected and whether Microsoft .NET Framework 3.5 Service Pack 1 is already installed.

Failed This computer does not have the Microsoft .NET Framework 3.5 Service Pack 1 installed. If the operating system is Windows Server 2008, download and install Microsoft .NET Framework 3.5 SP1 from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=22. If the operating system is Windows 8.1, Windows Server 2012 R2, Windows 8, Windows Server 2012, Windows 7 or Windows Server 2008 R2, enable the .NET Framework 3.5 feature. A computer restart may be required after installing.

Sometimes, you may see that this rule fails even after you enabled the NET Framework 3.5. So, generally when this issue happens, check from Control Panel -> Programs and Features and verify if NET Framework 3.5 is enabled or not. If it is not enabled, then go ahead and enable it and reboot the server. If it is already enabled and if you are still hitting this error, then try to disable the NET Framework 3.5 and re-enable it back, and then reboot the server. If issue still persists then try to run repair of .NET Framework using the .NET Framework Repair Tool mentioned in the article

What are SQL Server Setup or Installation Rules?
SQL Server Setup validates your computer configuration before the Setup operation completes. During SQL Server Setup, the System Configuration Checker (SCC) scans the computer where SQL Server will be installed. The SCC checks for conditions that prevent a successful SQL Server setup operation. Before Setup starts the SQL Server Installation Wizard, the SCC retrieves the status of each item. It then compares the result with required conditions and provides guidance for removal of blocking issues.
The system configuration check generates a report which contains a short description for each executed rule, and the execution status. The system configuration check report is located at %programfiles%\Microsoft SQL Server\120(may be different for other versions)\Setup Bootstrap\Log\\

This is applicable on below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

AlwaysON Availability Group Does Not Failover to second Node Upon Stopping SQL Server Services

SQLServerF1

When SQL Server AlwaysON Availability Groups are setup in SQL Server 2012 or SQL Server 2014, DBAs perform various testing by shutting down one node and verifying that the Availability Group fails-over to the second node and another approach tried is manually stopping the SQL Server services and expect the Availability Group to failover to the second node, but sometimes you may see that the failover does not occur, instead the AlwaysON AG and IP address resources get into failed state. Manually failing over the AlwaysON AG to the second node however successfully brings the resources and Availability Group online and accessible.

This issue generally happens if the failover has been attempted multiple times which exceeds maximum number of failures supported for the availability group during a given time period. This is not specific to AlwaysON rather can also happens with SQL Server Failover Cluster instances as well.

The Default value for the maximum number of failures during this period is n-1, where n is the number of WSFC nodes and the default time period is six hours. If an availability group exceeds its WSFC failure threshold, the WSFC cluster will not attempt an automatic failover for the availability group. Furthermore, the WSFC resource group of the availability group remains in a failed state until either the cluster administrator manually brings the failed resource group online or the database administrator performs a manual failover of the availability group. Ref

With default values, Automatic failover only happens once during six hours. So, the solution to this problem is to change the failover-threshold values for a given availability group. We can use the WSFC Failover Manager Console and increase “Maximum Failures in the specified period” to higher value based on our requirement and reduce the “Period (Hours)”. For example, we can set the “Maximum Failures in the specified period” to a value like 10 and “Period (Hours)” to 1 hour, which means in one hour AlwaysOn Availability Group will be tried to failover for 10 times. If it fails for 10 times, then it will be left in failed state and DBA has to investigate based on the cause of the failure and fix it after which it can brought online manually. The values are to be set based on your requirement and may vary in different environments.

This is applicable on below versions of SQL Server

SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Important SQL Server Alerts for Monitoring Incidents and Performance

SQLServerF1

It is important for SQL Server DBA to configure alerting for the SQL Server instances so that an E-Mail or a alert is sent to the DBA team in case of any problems which are critical or warnings. Handling and fixing the warnings will avoid any critical events from happening. It is not possible to monitor all the servers and instances 24/7, so there should be either a custom solution or third party monitoring tool to detect and send alerts when certain condition is met.

When preparing a custom solution or enabling alerts on 3rd party tools, we need to identify the list of important alerts which are to be monitored and how frequent these checks are to be made and the threshold on which the alerts are to be sent to the DBA team. Also, need to understand which of these are critical alerts and which can be warning level alerts.

Below are some of the common and important alerts with their description, Severity and what should be the general threshold for these alerts to be chekecd on.

Critical Alerts – These alerts generally cause the system to go down or can cause an outage. These alerts should be identified and resolved quickly and in case the issue is not resolved, this should keep sending the alerts. Also there should be option to temporarily snooze or turnoff the alerts which DBA team is working on them and they expect that it may take some time to fix the issue, this will avoid unnecessary alerts to be sent continuously.

Below are some of the Critical Alerts which need to be monitored and resolved immediately.

SQL Server Service State – This check should occur every 5 minutes and should check weather the SQL Server service is running or stopped. This should check for all the SQL Server services and SQL Server Agent services, etc

SQL Server Connectivity – This check should occur every 5 minutes to check if connections can be made to the SQL Server instance.

SQL Server Cluster Failover Event – On cluster servers, we need to have check which can detect any failover events with the SQL Server cluster resources and also should provide the state of SQL Server cluster resources. This check should occur every 5 minutes.

SQL Server Errorlog Scanner – SQL Server Errorlog consists of various errors, warning messages and informational messages. As this contains a mix of critical, warning and informational messages, we need to have this check run every 5 minutes. There should be customization allowed to list the messages which can be ignored, so that the alert for informational and some warning messages can be suppressed and alerts need not be sent for those.

CPU Check – This is a windows level check which need to identify the CPU usage on the server and raise alert if the CPU condition is consistently high like above 90%. This check is to be made every 5 minutes.

Memory Check – This is a windows memory check to identify if there is any low memory condition on the server which can affect all the services running on that server. This check needs to be run every 5 minutes.

Low disk space – This check needs to identify the free disk space going down to very low like less than 10% free disk space. This can soon cause an outage as there will not be any more room for the database files too grow on the disk.

Blocking – Blocking in SQL Server is considered critical as it can cause disruption to the application and the users will face issues. This needs to be checked every 5 minutes and to be alerted. There should be mechanism to snooze the alert which DBA is looking into the issue.

Warning Alerts – These alerts generally do not cause any outage or no immediate action is required, but is important to troubleshoot and fix it as soon as possible to avoid it from escalating to an critical alert. The threshold for these alerts can be little relaxed compared to critical alerts.

Below are some of the Warning Alerts which need to be monitored and resolved in a timely fashion to avoid escalating in to Critical alerts.

Disk space below 20% Threshold – This should check of the free disk space on any drive falls below 20% free, then alert needs to be sent to DBA team. DBA can check and identify the cause of low disk space and try to resolve immediately and it can go further low and can raise a critical alert or can cause an outage if there is no more free space left.

Backup Checks – It is important for the DBAs to make sure there are regular backups of all the databases. This check can be made daily once or twice to check of the Full and Log backups are happening regularly. Also, separate check can be used to just check the log backups and the check can be scheduled to run more frequently on important systems.

Job Failures – This check should check for any failures with any jobs. This check can be configured to check every 5 minutes. Depending on the importance of the job, DBAs can look into it in case of any job failure.

Long Running Queries – This check should send list of long running queries which are taking more time like more than 15 seconds or any other threshold based on the baseline.

Suspended Sessions – Although it is not common, but some environments can have too many suspended sessions thus causing exhaustion of all the available worker threads thus leading to an outage. So, it would be good to check every 15 minutes to see if there are too many suspended sessions.

Errors or Warnings from EventsLogs – There can be some critical or warning events that may have been raised in the windows Application or System eventlogs, so it is important to send alerts for such errors. This can be configured to send the list of errors or warnings every day once or few times in a day.

There can be many other alerts that can be configured and may be specific to each environment, but above should be monitored on all the server running SQL Server instances.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Third Party Monitoring or Alerting Tools for SQL Server

SQLServerF1

In most of the organizations use certain monitoring tools to monitor all or subset of the servers in their environment and the monitoring tool will raise alerts to the DBAs and other groups when a certain alert condition is met. It is very important to have some alert mechanism because it is not possible to manually monitor all the servers 24×7 and the problems can arise at any time. No matter how proactive a DBA team is, still there are unexpected problems with SQL Server either directly caused by SQL Server or due to issues related to Hardware, Network, Operating System or other applications or tools.

One of the important role of SQL Server DBA is to respond to alerts received and fix them in timely fashion based on the severity. Some very senior DBAs may be involved in making decisions on which monitoring product to be used or if a custom monitoring solution is to be developed. Most often or not most organizations prefer using third party monitoring tools to avoid unnecessary costs of developing and maintaining the in house tools.

There are various third party monitoring tools available in the market so we need to choose the correct article which suits our requirement. This article has information regarding the processes or Criteria that can be followed to determine which Third party monitoring tool best suits your environment.

Below are some of the popular 3rd party Monitoring tools available in the market.

SQL Spotlight by Quest – SQL Spotlight from Quest monitoring tool is a very mature tool and provides many features or consists of many functionalities. This tool allows us to customize the alerts, not just the selection of the alerts but also we can customize the thresholds and severity. It can be customized to ignore alerts which are not required and allows to disable the alerts temporarily while the DBA team is working on the issue. This has central server where one can view various reports. There are limited options for level of details for SQL Server Monitoring and there is web based console for monitoring alerts.

SQL Diagnostic Manager by Idera – SQL Diagnostic Manager is one of the most popular and widely used monitoring tool. It provides basic alerting features and performance and server-monitoring options. It Provides a comprehensive view of current performance. Alerts and Thresholds can be configured and alerts can be snoozed during maintenance. This tool provides a central console from where we can manage all the servers and instances and also generate various reports. It is simple to install, configure and use, also the product support is good too. The price of the product is on higher side as the licensing mode in per SQL Server instance.

Microsoft Systems Center 2012 (SCOM) – Systems Center is a very powerful tool that can be used to monitor SQL Servers, Windows Servers and Windows Workstations. This comes with easy to use central management interface and Integrates well with all Microsoft products and can be used on Windows and SQL Servers. We can manage and generate reports from centralized server. However this tool is not easy to configure and lacks monitoring SQL Server compared to other third party monitoring tools. This has a complex licensing model involving per processor licenses.

SQL Monitor by RedGate – SQL Monitor from Red Gate is a lightweight tool which is easy to install and configure and simple to use. This tool is an effective monitoring solution for simple to moderately complex environments. The licensing model of this tool is per instance which makes it costlier compared to other tools with similar features. This can be bundled with other tools like Backup tools and works well.

ApexSQL Monitor – ApexSQL Monitor tool provides alerting and performance monitoring options. All monitored performance parameters can be viewed from the dashboard on central server. We can see real time activity as well as we can configure it to send alerts to the DBA groups for various alerts. ApexSQL Monitor doesn’t install any agents to collect performance metric values. The data is collected using the tools already available in the operating system and SQL Server. This approach eliminates the need for installation of any additional agents, thus avoiding the situation when agent installation is not possible due to insufficient permissions and company policies.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 
1 2 3 4 8