SQL Server AlwaysON Availability Group Common Errors or Failures

SQLServerF1

AlwaysON Availability Groups has been introduced with SQL Server 2012 and has been a very popular and most used feature as it provides both High Availability (HA) and Disaster Recovery (DR) solutions. As it is a new feature, there are many issues which DBA’s face while configuring AlwaysON Availability Groups.

Below are some of the common errors or failures related to SQL Server AlwaysON Availability Groups.

Checking for compatibility of the database file location on the secondary replica resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)
The following folder locations do not exist on the server instance that hosts secondary replica Node1\AGTest: S:\MSSQL11.AG1\SQL_DATA; Microsoft.SqlServer.Management.HadrTasks)

– This error occurs if the drive letters or the folder path does not match between primary and secondary replica.
– To resolve this error, make sure that same drive letter and folder path exists on both the servers or perform manual synchronization of secondary server databases using backup/restore.

Failed to create, join or add replica to availability group ‘AGTest’, because node ‘Node1’ is a possible owner for both replica ‘AGTest\AGTest’ and ‘Node1\AGTest1’. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again. (Microsoft SQL Server, Error: 19405)
– This error occurs, if SQL Server instance where you are setting up AlwaysON Availability Group is part of SQL Server failover clustering and has both nodes as possible owners and then You tried to add another availability Group for another SQL cluster instance involving these nodes.
– To resolve this error, either follow the solution suggested in the error message “If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again” or instead of using cluster SQL instances, just use standalone instances.

Error while trying to connect to AlwaysON Availability group using Listener Name
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

– This error occurs if the port number of the Listener is not 1433. To resolve this issue either use the port number along with the listener name like SQLAGLSTNR,1467 or create an alias.
– Make sure listener port is open to communicate. Test using telnet listenername portnumber
– Communication only works when using TCP network protocol.

Database Mirroring login attempt by user ‘Domain\ComputerName$.’ failed with error:
‘Connection handshake failed. The login ‘Domain\ComputerName$’ does not have CONNECT permission on the endpoint. State 84.’

– This error will be logged in SQL errorlog when trying to setup AlwaysON or Database Mirroring. This error occurs if the SQL Server services are running under local system accounts or if the SQL Service account does not have connect permission on the endpoint.
– To resolve the error, Change the SQL Server services to run under a domain account and then grant connect permission on endpoint to SQL Server service account.
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\SQLServiceAccount]
GO

Summary for the replica hosted by ServerName\INSTANCE2 Replica mode: Asynchronous commit
This replica will use asynchronous-commit availability mode and support only forced failover
(with possible data loss).
Note: This is a Failover Cluster Instance. Failover Cluster Instances do not support AlwaysOn automatic failover.

– This error occurs, if you are using SQL clustered instances to setup AlwaysON Availability Group.
– Cluster SQL Server instance will not support the automatic failover in availability groups. If you want automatic failover, install SQL server instance as standalone instance.

Cluster network name resource ‘TestAG’ failed to create its associated computer object in domain ‘testdomain.com’ during: Resource online.The text for the associated error code is: A constraint violation occurred.Please work with your domain administrator to ensure that:
The cluster identity ‘SQLClus$’ has Create Computer Objects permissions. By default all computer objects are created in the same container as the cluster identity ‘SQLClus$’.The quota for computer objects has not been reached. If there is an existing computer object, verify the Cluster Identity ‘SQLClus$’ has ‘Full Control’ permission to that computer object using the Active Directory Users and Computers tool.

– This error occurs if Cluster Name Object does not have rights to create a new Virtual Network Name Object. Refer below article for instructions to prestage the Virtual Name Object.
http://technet.microsoft.com/en-us/library/dn466519.aspx

Joining database on secondary replica resulted in an error.
(Microsoft.SqlServer.Management.HadrTasks)
Failed to join the database ‘AGTest’ to the availability group ‘AGGrpTest′ on the
availability replica ‘Replica2′. (Microsoft.SqlServer.Smo)
The connection to the primary replica is not active. The command cannot be processed.
(Microsoft SQL Server, Error: 35250 Level 16, State 7)

– Try below steps to fix the error
o Make sure that the alwaysON endpoint [Hadr_endpoint] is not blocked by firewall
o Verify and make sure that SQL Server service account of primary server is added as a login on all the secondary servers and vice-versa.
o If SQL Server service accunt is “Nt service\” or local system account then ensure system account (Domainname\systemname$) of each replica is added as a login to other replicas.
CREATE LOGIN Domain\replica2$] FROM WINDOWS
o Grant connect permission on alwaysON endpoints on each replicas for SQL Server service account of all other replicas On Secondary replica run below query by changing the domain and replica server names
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\replica1$]
On primary replica run below query by changing the domain and replica server names
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\replica2$]
o Make sure SQL Server name (select @@servername) matches with hostname of the server.
o Make sure cluster service startup account is added as SQL Server login.

Error while trying to setup AlwaysOn availability group
Operating System Error 1265(The system cannot contact a domain controller to service the authentication request. Please try again later.).

– This error can occur if the password of inter-domain trust account is not synchronized on both sides of the trust relationship.
– Refer below link to see if it helps fixing the error, else engage your domain Administrator.
http://support.microsoft.com/kb/816577/en-us

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.

 

SQL Server Management Studio (SSMS) Common Errors or Failures

SQL Server Management Studio (SSMS) by default gets installed on all servers where we install SQL Server database engine or related components. SQL Server DBA’s often use SSMS in order to manage the SQL Server instances for tasks like creating databases, creating logins, users and granting permissions, Creating maintenance plans or jobs, checking for any errors and troubleshoot various issues with SQL Server instance by running queries. SSMS has become one of the most important tool for a DBA to function their job. Most of the time, there wont be any errors with SQL Server Management Studio (SSMS), but at times, we may end up with some errors while using SSMS and we need to fix them quickly.

SQL Server Management Studio is dependent on many other components like Visual Studio, .Net Framework, etc, so changes to any of these components can also result in the failure or errors with SSMS.

Below are some of the common errors which we may see with SQL Server Management Studio (SSMS).
Error while trying to create new database diagram, which is crashing the SSMS with error
“Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft.VisualStudio.OLE.Interop)”

You can resolve this issue by following below steps
– Copy and replace the DLL “dsref80.dll” in below path from a machine where SSMS is working fine in to the machine where the problem exists.
C:\Program Files (x86)\Common Files\microsoft shared\Visual Database Tools\dsref80.dll
– Replace all the files in below folder using the files from another server where SSMS is working fine.
C:\Program Files (x86)\Common Files\microsoft shared\MSDesigners8\
– Close and Open SSMS.

Error in SQL Server Management Studio: Failed to start debugger. Additional information: The EXECUTE permission was denied on the object ‘sp_enable_sql_debug’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)
– You may receive above error message if you try to use the Debug button in SSMS, if it was by mistake, then no need to worry, but if you want to use the debug option in SSMS, then make sure below are true.
o SQL Server Management Studio must be running under a Windows account that is a member of the sysadmin fixed server roll.
o The Database Engine Query Editor window must be connected by using either a Windows Authentication or SQL Server Authentication login that is a member of the sysadmin fixed server role.
o The Database Engine Query Editor window must be connected to an instance of the SQL Server 2008 Database Engine. You cannot run the debugger when the Query Editor window is connected to an instance that is in single-user mode.

Error while trying to start SQL Server 2012 Management Studio (SSMS)
Cannot find one or more components. Please reinstall the application.
Exception has been thrown by the target of an invocation

– You may receive above errors while trying to open SSMS after you install Visual Studio 11. Visual studio might have been installed separately or may have been installed as part of some other software installation.
– To resolve this issue, follow the below steps
o Open registry editor from Start -> Run -> Type regedit
o In registry Editor navigate to HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio
o Backup and Delete the key 11.0_Config
o Start the SQL Server Management Studio and it should not open without any more errors.
– Restart ssms.exe

Operation not supported on SQL Server 2005
– You may received above error if you are using SQL Server 2005 Management Studio and connected to higher version of SQL instance like SQL 2012 or SQL 2008 R2 and then use a feature which was introduced in these higher versions. SSMS 2005 cannot be used to use features introduced in higher versions of SQL Server, you need to install and use higher version of SQL Server management Studio(SSMS)

microsoft .net framework error.unable to read the list of previously registered servers on this system.Re-register your servers in the registered servers windowvalue cannot be null.parameter name:viewinfo
(Microsoft.sqlserver.managment.sqlstudio.explorer)
Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializationException: Serialization operation on
ServerGroup[@Name=’SqlServerCompactEditionServerGroup’] has failed.
System.IO.DirectoryNotFoundException: Could not find a part of the path ‘c:\winnt\temp\qhn8zgxb.tmp’.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

– This error can occur if the user profile is corrupted. Normally we RDP to the server where SQL Server is installed using a domain account or local account and then open SSMS and then try to connect to SQL Server instance, if the profile of this domain or windows account is corrupt, then this error occurs.
– To resolve this error either create a new windows or domain account and login using the new account and connect to SSMS and then to SQL Server instance or repair the profile of the existing account.

The File %CommonDir%\dte80.olb could not be loaded. An attempt to repair this condition failed because the file could not be found. Please reinstall this program.”
The application cannot start

– This error can occur if one or more files related to SSMS is corrupt or missing. dte80.olb is a shared file used by the Visual Studio shell, Management Studio, Books Online, and the MSDN help viewer.
– To resolve this issue, either uninstall and reinstall the SSMS component or check if
C:\Program Files\Common Files\Microsoft Shared\MSEnv\dte80.olb file exists, if it does not exist, then copy this file from another server which has same SQL Server version and build, but better option is to reinstall the SSMS component.

Value cannot be null.
Parameter name: viewInfo (Microsoft.SqlServer.Management.SqlStudio.Explorer)

– There can be various reasons why you may see this error. Follow below to resolve this error
o Make sure that C:\ drive has enough free space.
o Launch SSMS by right click and choose run-as-administrator
o Create empty folder with name 2, if it does not exist in below path, so that you can navigate to below folders.
C:\Users\\AppData\Local\Temp\2
o Make sure from start>run>%temp% and start>run>%tmp% folders can be opened without any error, if it returns any error, contact your server administrator to fix the same.
o Apply latest Service pack or Cumulative Update
o Uninstall and reinstall SQL Server Management Studio

Evaluation Period has expired. For information how to upgrade your evaluation software please go to. Microsoft SQL Server Management Studio Complete (expires in x days)
– This error occurs, if you have installed SQL Server Management Studio or Database Engine using SQL Server Enterprise Evaluation Edition, which will expire after 180 days.
– You need to perform an Edition upgrade to an licensed version of SQL Server to fix the error.
– Sometimes, ever after successful edition upgrade, still you may receive this error, and to fix the error, set the CommonFiles registry key value to 3 under
HKLM\SOFTWARE\\Microsoft\Microsoft SQL Server\100\ConfigurationState and then rerun the edition upgrade.

Microsoft SQL Server Management Studio
Object reference not set to an instance of an object.
(Microsoft.VisualStudio.Platform.WindowManagement)

– Try below options to see if it resolves the error
o Repair the .net framework
o Open a command prompt by choosing run asa dministrator and then run below commands
C:\Program Files\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files\Internet Explorer>regsvr32 ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 ieproxy.dll
regsvr32 actxprxy.dll
o Uninstall SSMS and reinstall SSMS

There are various other errors which cause failure of SSMS and will try to add as many as possible.

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.

 

Important SQL Server Configuration Options to be enabled on SQL Server Instance

As a DBA, we would need to make sure that the SQL Server is configured optimally for better performance, stability and security. There are various configuration settings in SQL Server which allows us to customize our SQL Server environment to the requirements of the specific Applications. One of the common SQL Server configuration settings can be found and can enabled using any of the below commands.

SP_Configure

Or

select * from sys.configurations

The results returned by sp_configure contains name of the configuration item, minimum and maximum values that can be configured for a particular setting and the config_value and run_value of this setting. Additionally sys.configurations system table contains more details about the configuration settings. Config_value means, it has been enabled or not, but still having this set for a setting will not be in effect until the run_value also shows the same value as config_value.

Example: Below shows config_value of “backup compression default” is disabled and “blocked process threshold (s)” is set to 5

name

minimum

maximum

config_value

run_value

backup compression default

0

1

0

0

blocked process threshold (s)

0

86400

5

5

At the bottom, you can find the table with list of all the SQL Server configuration options available in SQL Server 2008 R2.

By default, we will not be able to see all the configuration values using sp_configure, most of them will be hidden. Inorder for us to be able to see all the configuration settings, we need to run below command as a sysadmin. Now we will be able to see all the configuration settings.

sp_configure 'show advanced options',1
Go
Reconfigure with Override
Go
Sp_Configure
Go

Now let’s see how we can enable a particular configuration setting. Below is the command used to enabled “backup compression default” setting.

sp_configure 'backup compression default',1
Go
Reconfigure with Override
GO
sp_configure
GO

– Now, we can see that the backup compression default has been enabled and is used as well.

name

minimum

maximum

config_value

run_value

backup compression default

0

1

1

1

– There are many configuration options available, but in general most of these are better left untouched, but some are very important to be configured in most of the environments for better performance, stability and security. Below are some of those settings and some brief details about the same.

affinity mask – There are about 4 various of this setting listed in sp_configure which related to CPU and I/O affinity. This is better to be left at default, however on one server if there are multiple instances of SQL Server running, we can configure these values, so that we can limit a particular SQL Server instance to use only sub-set of available processors on the server, thus segregating the workload of the processors to each SQL Server instance. But proper testing needs to be done to make sure it does not lead to performance problems.

awe enabled – This setting is for x86 based systems, where we would like the SQL Server instance to use more than 4 GB of physical memory. X86 systems are limited to use up to maximum of 4 GB of physical memory, but using AWE option, we can enabled SQL Server to use more than 4 GB memory. This should be enabled on x86 based dedicated SQL Server systems with more than 4 GB of memory.

backup compression default – This option is available starting with SQL Server 2008. This should be enabled, so that the size of backup files will be reduced thus saving disk space and improves the performance of database backup as there is less amount of I/O operations to be performed.

Database Mail XPs – Enable this option, if you would like to use Database Mail feature, so that E-Mails can be sent from SQL Server.

default trace enabled – This is enabled by default and collects trace with details like Auto-growth or Shrink related events on databases, any changes to the schema of the objects, any server or database configuration changes, etc. There is not much overhead of running default trace, so better to leave this running and can be useful sometimes to know, if there were any activities like auto-growth or schema changes. Also, there will be only 5 trace files with 5 MB each will be retained, so on a OLTP system with high number of transactions, this can have information collected for less duration.

filestream access level – If you are using filestream feature, then enable this option, else you can leave this disabled.

max degree of parallelism (MAXDOP) – This is one of the important options which shows affect on SQL Server performance. It is advised to change this value which is appropriate to the Application. There are some recommendations on what value to set for this setting and depends on the type of Application(vendor application or custom home-built), type of workload(OLTP or DataWarehouse), type of transactions(Many small and few large queries or many large and few small transactions). Below articles should provide insights on setting this value.
http://support2.microsoft.com/kb/2806535
http://blogs.msdn.com/b/psssql/archive/2013/09/27/how-it-works-maximizing-max-degree-of-parallelism-maxdop.aspx

max server memory (MB) – This is another important setting which has to be configured. This basically limits the bufferpool memory region of SQL Server Memory. Generally, start with setting this value to 85% of total RAM on a dedicated SQL Server machine with one SQL Server instance and monitor the memory usage. Basically, we need to ensure that SQL Server does not use up entire memory on the server and causing memory pressure for other applications or to Operating System itself as it may cause server to hang or crash. Monitor the memory usage after setting this value, if still the Available memory on the server is falling to very low, then monitor and see which other application is consuming memory and if that application can be moved to other server. If other Application has to run on same server as SQL Server, then need to lower the Max Server Memory value, so that other application and OS has enough memory for their functioning. Note that this value is set interms of MB, so just be mindful.

optimize for ad hoc workloads – This setting is disabled by default and is better to be left this way, but in some environments where there are many Adhoc(queries not part of Stored Procedures) commands being run from application, then enabling this this setting will help with performance.

priority boost – This is disabled by default and ensure this is always in disabled state. Enabling this can cause serious problems to the system.

Rest all options, you may choose to leave as default or carefully consider making changes if it really has any benefits for your environment.

Below are all the SQL Server configuration settings available in SQL Server 2008 R2.

name

minimum

maximum

config_value

run_value

access check cache bucket count

0

65536

0

0

access check cache quota

0

2147483647

0

0

Ad Hoc Distributed Queries

0

1

0

0

affinity I/O mask

-2147483648

2147483647

0

0

affinity mask

-2147483648

2147483647

0

0

affinity64 I/O mask

-2147483648

2147483647

0

0

affinity64 mask

-2147483648

2147483647

0

0

Agent XPs

0

1

1

1

allow updates

0

1

0

0

awe enabled

0

1

0

0

backup compression default

0

1

1

1

blocked process threshold (s)

0

86400

5

5

c2 audit mode

0

1

0

0

clr enabled

0

1

0

0

common criteria compliance enabled

0

1

0

0

cost threshold for parallelism

0

32767

5

5

cross db ownership chaining

0

1

0

0

cursor threshold

-1

2147483647

-1

-1

Database Mail XPs

0

1

1

1

default full-text language

0

2147483647

1033

1033

default language

0

9999

0

0

default trace enabled

0

1

1

1

disallow results from triggers

0

1

0

0

EKM provider enabled

0

1

0

0

filestream access level

0

2

0

0

fill factor (%)

0

100

0

0

ft crawl bandwidth (max)

0

32767

100

100

ft crawl bandwidth (min)

0

32767

0

0

ft notify bandwidth (max)

0

32767

100

100

ft notify bandwidth (min)

0

32767

0

0

index create memory (KB)

704

2147483647

0

0

in-doubt xact resolution

0

2

0

0

lightweight pooling

0

1

0

0

locks

5000

2147483647

0

0

max degree of parallelism

0

1024

0

0

max full-text crawl range

0

256

4

4

max server memory (MB)

16

2147483647

119808

119808

max text repl size (B)

-1

2147483647

65536

65536

max worker threads

128

32767

0

0

media retention

0

365

0

0

min memory per query (KB)

512

2147483647

1024

1024

min server memory (MB)

0

2147483647

0

0

nested triggers

0

1

1

1

network packet size (B)

512

32767

4096

4096

Ole Automation Procedures

0

1

0

0

open objects

0

2147483647

0

0

optimize for ad hoc workloads

0

1

0

0

PH timeout (s)

1

3600

60

60

precompute rank

0

1

0

0

priority boost

0

1

0

0

query governor cost limit

0

2147483647

0

0

query wait (s)

-1

2147483647

-1

-1

recovery interval (min)

0

32767

0

0

remote access

0

1

1

1

remote admin connections

0

1

0

0

remote login timeout (s)

0

2147483647

20

20

remote proc trans

0

1

0

0

remote query timeout (s)

0

2147483647

600

600

Replication XPs

0

1

0

0

scan for startup procs

0

1

0

0

server trigger recursion

0

1

1

1

set working set size

0

1

0

0

show advanced options

0

1

1

1

SMO and DMO XPs

0

1

1

1

SQL Mail XPs

0

1

0

0

transform noise words

0

1

0

0

two digit year cutoff

1753

9999

2049

2049

user connections

0

32767

0

0

user options

0

32767

0

0

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.

 

Adding New Articles to Transactional Replication without Generating Snapshot of All Articles

SQLServerF1

In Transaction replication which was already setup and If we want to add new articles, then a new Snapshot has to be generated and applied to the Subscriber, but by default snapshot of all the existing and new articles will get generated and applied to the Subscriber, which is not what we want as it can take long time when the database is large and causes Subscriber to be unavailable while applying the Snapshot. There is a way in which we can avoid this behavior and just generate the Snapshot of the newly added articles and apply them to the Subscriber, which will not cause any problem to the existing articles which can be available during this process.

Let’s see how we can achieve this.

Run the below commands on the Publication database

sp_helppublication

If the columns “immediate_sync” and “allow_anonymous” are having a value of 1(Enabled) for both of them, the Snapshot of all the articles will be generated. As we do not want this behavior, we will change these values to 0(Disabled) for both the “immediate_sync” and “allow_anonymous” options.

We can disable these options by running below commands on Publication database

EXEC sp_changepublication 
@publication = 'your publication name', 
@property = 'ALLOW_ANONYMOUS' , 
@value = 'False' 
GO 

EXEC sp_changepublication 
@publication = 'your publication name', 
@property = 'IMMEDIATE_SYNC' , 
@value = 'False' 
GO 

Now, we can go ahead and add the new article to the publication from GUI, and then we need to start the Snapshot Agent, which will generate new Snapshot just for the new articles. If we have not run the snapshot agent then the newly added article will be in Subscribed state, but will not be active, Inorder to make this active, we need to run the Snapshot agent job.

We can check article status on Subscriber database by running the Subscriber database.

sp_helpsubscription

Do not follow these steps directly on production environment, please test the solution on test server to get comfortable and test if it is working as expected.

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 Service Startup Failure Common Errors

As a DBA we encounter issues where SQL Server fail to start after reboot of server or after making some configuration changes. It is very critical to bring the SQL Server online immediately as it impacts applications leading to escalations.

First, we need to understand on where to check if SQL Server fails to start.
– Try to start SQL Server from Configuration manager on standalone servers and on cluster servers, bring the SQL Server resources ONLINE.
– Check if the SQL Serve errorlog is created. You can find the location of the SQL Server errorlog file from Start -> All Programs -> Microsoft SQL Server 2008 or latest version installed -> Configuration Tools -> SQL Server Configuration Manager -> Select SQL Server Services on the left side -> On the right side right click on SQL Server (MSSQLSEVER or instance name) and select Properties -> Click on Advanced Tab(in SQL 2012 or higher there will be separate tab called startup parameters) -> you can see the location of SQL Server ErrorLog file as mentioned next to the “-e” startup parameter .
– Check and see if new errorlog file is getting created when you start the SQL Service, if it is not getting created then check the Event viewer Application and System logs which should have some errors related to the cause of the failure.
– If Errorlog file is created, then look into the file and look for any errors.

Although there could be variety of reasons for the failure, here we try to document most common ones and add more to the list going forward.

Error 1069: The service did not start due to a logon failure.
– This error occurs if the SQL Server service account password in incorrect. Change the password to the correct one from SQL Server configuration manager and then restart the SQL Server service.

The SQL Server (MSSQLSERVER) service failed to start due to the following error: The service did not start due to a logon failure.
– This error occurs when the password provided for the SQL Server service account is incorrect. Open SQL Server Configuration Manager and then provide the correct password.
– If this issue is happening after every reboot of Server, then server might not be able to immediately communicate with Active Directory (AD) or some policy in DC leading to the failure. In this case, you may choose to start the SQL Server service with “(Automatic) Delayed Start” option, which can be configured from registry HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\ and then create a new DWORD DelayedAutoStart and set it to 1 and also ensure Start value key is set to 2. You
can also set this from services console(services.msc), open SQL Server service properties and change the startup type to (Automatic) Delayed Start.

Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\MSSQLSERVER ]. Error: 0x50Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x50, status code 0x50.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x50, status code 0x1.
Error: 17826, Severity: 18, State: 3.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
Error: 17120, Severity: 16, State: 1.
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

– This error occurs, when incorrect values are provided to the Named Pipes in the SQL Server Configuration Manager. For default instance of SQL Server, change the Named Pipe name Protocols for MSSQLSERVER in configuration manager to \\.\pipe\MSSQLSERVER\sql\query and for named instance use the named pipe as \\.\pipe\MSSQL$\query
– Restart the SQL Server instance or better Reboot the server.

Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed. See “Configuring Certificate for Use by SSL” in Books Online.
TDSSNIClient initialization failed with error 0x80092004, status code 0x80.
TDSSNIClient initialization failed with error 0x80092004, status code 0x1.

– The error occurs, when SQL Server is unable to use the SSL certificate provided in the SQL Server configuration manager. To fix the issue make sure SSL certificate meets all the requirements to be used for SQL Server.
http://support.microsoft.com/kb/316898

Error: 26023, Severity: 16, State: 1.
Server TCP provider failed to listen on [ ‘any’ 3136]. Tcp port is already in use.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x2740, status code 0xa.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x2740, status code 0x1.
Error: 17826, Severity: 18, State: 3.

– This error occurs, if the TCP port which SQL is listening is on is already being used by some other process. You need to find the process which is using this port or change SQL Server TCP port to some thing else which is available to use.

The request failed or the service didn’t respond in a timely fashion.Consult the event log or other applicable error logs for details.
Could not open error log file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG’. Operating system error = 5(failed to retrieve text for this error. Reason: 1815)

– Operating system error = 5 means access denied. Grant full control to the SQL Server service account Microsoft SQL Server folder where you have your SQL Server binaries, database files and Errorlog files and all sub-folders.

Error: 17113, Severity: 16, State: 1.
Error 2(The system cannot find the file specified.) occurred while opening file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf’ to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

– Above error occurs if master.mdf file is not found in the location mentioned or if SQL Server does not have permission to access this file, so make sure the master.mdf file exists in that path and Full permissions are granted to SQL Server service account.

Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf for file number 2. OS error: 32(error not found).

– This error occurs if the mastlog.ldf file is not accessible by the SQL Server process, possibly because this file is used by some other process.

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.

 

Decoding SQL Server Startup Process and Checking for Errors

There are various tasks that take place when SQL Server is started in a particular sequence. If there are any failures, it will be reported in the SQL Server Errorlog. In this article let’s try to understand what SQL Server does while it is starting up. There are many internal tasks which SQL Server performs, but will not be covered here as our focus is to understand what SQL Server is doing for a general user perspective by decoding the SQL Server Errorlog file.

Below is the snipped of SQL Server 2008 R2 errorlog, broken down into sections to understand what action is being performed.

At the very start of SQL Server we can find the details about SQL Server Version, Edition, Platform and Operating System related details.
Server Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)
Apr 25 2011 16:43:45
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7600: ) (VM)
Server (c) Microsoft Corporation.
Server All rights reserved.

Next, we can find the process ID of SQLServr.exe process for this SQL Server instance. If it is a VMware system, we can find those details too

Server Server process ID is 566.
Server System Manufacturer: ‘VMware, Inc.’, System Model: ‘VMware Virtual Platform’.

Below shows us which Authentication Mode is SQL Server instance currently configured to allow connections. Mixed means, it allows both Windows and SQL Server Authentication.

Server Authentication mode is MIXED.

Next, we can find the location of the SQL Server Errorlog file where informational, Warning and Error Messages of SQL Server are logged.

Server Logging SQL Server messages in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG’.

We can next fine, what the the process ID of SQL Server used last time before the restart of SQL Server. Next, we can see SQL Server reads the registry details and identified the path of Master database .mdf and .ldf files locations.

Server This instance of SQL Server last reported using a process ID of 1844 at 5/21/2012 5:23:23 AM (local). This is an informational message only; no user action is required.
2011-03-28 19:13:01.850 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

The checks for the priority with which SQL Server should start and logs the same. Then there will be additional details identified like number of CPUs on the server detected by the SQL Server. It then checks if lock pages in memory privilege has been enabled for the SQL Server service account or not and logs if it is enabled. If any configuration to default lock settings was changed, it will read and shows the same.

Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
Server Detected 4 CPUs. This is an informational message; no user action is required.
Server Using locked pages for buffer pool.
Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

Next, SQL Server will start the Master database and then performs recovery the master database. If it fails in this process, it will be logged here and SQL Services will be stopped.

spid5s Starting up database ‘master’.
spid5s Recovery is writing a checkpoint in database ‘master’ (1). This is an informational message only. No user action is required.

Some additional details related to additional features usages is logged as shows below it shows information related to Resource Governor, SQL Server Audits, FileStream, etc.

spid5s Resource governor reconfiguration succeeded.
spid5s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
spid5s SQL Server Audit has started the audits. This is an informational message. No user action is required.
FILESTREAM: effective level = 0, configured level = 0, file system access share name = ‘MSSQLSERVER’.

If default trace configuration option is enabled, then it will start the same. Then it starts the internal hidden database Resource Database. We can see its version from here.

spid5s SQL Trace ID 1 was started by login “sa”.
spid5s Starting up database ‘mssqlsystemresource’.
spid5s The resource database build version is 10.50.1600. This is an informational message only. No user action is required.

We can notice the physical SQL Server server name. On cluster server, we can see the NetBios Name followed after this.

spid5s Server name is ‘Test_Server’. This is an informational message only. No user action is required.

Next Model database is started and same will be logged in the errorlog.

spid10s Starting up database ‘model’.

Additional information related to any SSL Certificates used by SQL Server or is it using the self-generated certificate is logged. Any failure using the SSL certificate will be logged here and then SQL Server will stop.

Server A self-generated certificate was successfully loaded for encryption.

SQL Server will check for the TCP/IP Port and Named Pipe details and same will be logged. Any failure using a particular port will be logged here.

Server Server is listening on [ ‘any’ 1433].
Server Server is listening on [ ‘any’ 1433].
Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
Server Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
Server Server is listening on [ ::1 1434].
Server Server is listening on [ 127.0.0.1 1434].
Server Dedicated admin connection support was established for listening locally on port 1434.

TempDB is restarted each time, SQL Server starts, so we will see messages related to tempdb cleaning and then starting.

spid10s Clearing tempdb database.
spid10s Starting up database ‘tempdb’.

Next, it will check for SPN settings and try to setup SPN, if SQL Service account has enough permissions to create. If it fails, it will be reported and NTLM authentication will be used instead of kerberos.

Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This i
Server SQL Server is now ready for client connections. This is an informational message; no user action is required.

Next, SQL Server starts and recovers all the user databases which are Online. Any failures starting a particular database will be logged here.

spid21s Starting up database ‘ReportServer’.
spid23s Starting up database ‘ReportServerTempDB’.
spid13s Starting up database ‘msdb’.
spid22s Starting up database ‘test_db’.

SQL Server startup completes here and any further messages logged will be related to activities performed on SQL Server which are configured to be logged in SQL Server Errorlog like, Full Backup and Log backup completion informational message, backup failure message, Login Failed Messages, any other warnings or errors.

Looking through the SQL Server Errorlog carefully will let us identify any problems causing SQL Server failure during startup or when it is stopped or restarted unexpectedly.

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.

 

Common Database Restore Failure Errors in SQL Server

One of the most common tasks for a DBA is to perform Restore of databases as part of recovering the data during an disaster or corruption or regular activities like refreshing databases from prod to Dev or Test server. While trying to perform the restore, we may encounter various errors causing the database restore to fail.

Below are some of the common errors related to Database Restore.

Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

– You may encounter this kind of error if you tried to restore a database and if has failed in the middle or cancelled it after sometime leaving a database in restoring state. To get rid of the database which is in restoring state, issue below command
DBCC DEATCHDB('DBNAME')
– Now you can perform a new restore.


Restore failed for Server ‘TestServer\Inst’. (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: The operating system returned the error ’32(The process cannot access the file because it is being used by another process.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘D:\MSSQLSERVER\MSSQL\DATA\TestDB.mdf’. (Microsoft.SqlServer.Smo)

– This error can occur if you are trying to restore a database and specifying the physical file names which are already being used by another database Or if you are restoring on top of another database, but not using “With Replace” option while restoring.
– So, make sure no other databases on the server are using the database files, and if you are sure, then perform the restore by selecting “With Replace” option. If the database files are used by other database, then specify a different physical name for the database files while performing restore.


Msg 3101, Level 16, State 1, Line 8
Exclusive access could not be obtained because the database is in use.

– This error occurs, if the database on top of which you are trying to restore is currently being used by some users, thus locks are being placed on the database. Unless these locks are released, you will not be able to perform the restore operation.
– You can first take the database into Single_User mode and using clause “rollback immediate” which will kill any active sessions for this database. Once the database is in single user mode, you can perform restore or delete the database as well if required.
Use master
go
Alter database test set single_user with rollback immediate
go

– Restore your database now


Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database ‘Test_DB’ because it is in use by this session. It is recommended that the master database be used when performing this operation.

– Change the context of your session to Master database and then take the database into single user mode as mentioned above and then perform the restore using the option “With Replace” which will overwrite the existing database files.


Restore failed for Server ‘Test_DB’. (Microsoft.SqlServer.Smo) System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘TESTDB’ database. (Microsoft.SqlServer.Smo)

– This error occurs if you are trying to restore on top of another database by using a backup of some other database. You can use the restore option “with Replace” in order for this to work.


Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases. Log file ‘D:\MSSQLServer\TestDB_Log.ldf’ does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Processed 34353 pages for database ‘TestDB’, file ‘TestDB_Data’ on file 1.
Processed 234 pages for database ‘TestDB_copy’, file ‘TestDB_Log’ on file 1.
RESTORE DATABASE successfully processed 34587 pages in … seconds

– This error can occur, if there was already TestDB_Log.ldf file which belong to a different database in that location D:\MSSQLServer\
– You can delete the physical files in that location and then perform the restore again to make sure there are no errors after restore.


Msg 3201, Level 16, State 2, Line 4
Cannot open backup device ‘D:\MSSQLServer\Backups\TestDB_Backup.bak’. Operating system error 2(error not found).

– The Operating System error 2 refers to “The system cannot find the file specified”, so make sure that the backup file name mentioned is correct and the file exists in the path specified.
– If above did not resolve the issue, check if the backup file is corrupted or not, by trying to restore with verifyonly or filelistonly and see if they return the details and also you can try using another latest backup to try ans restore.


System.Data.SqlClient.SqlError: The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘D:\MSSQLServer\Test_data.mdf’. (Microsoft.SqlServer.Express.Smo)

– This error occurs if SQL Server service account does not have enough permissions to create the Test_data.mdf file in the path specified. Grant Full permissions to SQL Server Service account to this path and its subfolder and files.


specified cast is not valid

– If you are receiving this error while trying to restore from GUI, then try restoring using T-SQL commands. Example, if backup is performed with option of securing the backup with a password, and if you try to restore this backup from GUI, you will get this error, so the solution would be to restore using T-SQL.


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.

 

Lot of Events in EventLog related to The database engine detached a database

SQLServerF1

On Windows Server 2012, you may see many messages as below logged into the Windows Eventlogs every few seconds. If you are seeing this message on a database server, then you may assume this is related to SQL Server, however this message has nothing to do with the SQL Server or any components installed along with SQL Server.

Event Id 327
svchost (1600) The database engine detached a database (2, C:\Windows\system32\LogFiles\Sum\SystemIdentity.mdb). (Time=0 seconds)
Event Id 326
svchost (1600) The database engine attached a database (2, C:\Windows\system32\LogFiles\Sum\SystemIdentity.mdb). (Time=0 seconds)

These message appear because of the “User Access Logging” service which comes with Windows Server 2012. This is enabled by default on servers running Windows Server 2012. This application aggregates client usage data by role and products on a local server which later can be used by IT administrators to find quantities and instances by server role or software product, by user, by device, by the local server, and by date using Windows Management Instrumentation (WMI) or Windows PowerShell cmdlets.

These messages can be sometime logged too frequently causing the eventlog file to become full or even can cause hang or crash of the server.

If you are not using this application “User Access Logging”, then you may consider disabling this service. Follow the below steps to Stop or Disable “User Access Logging”.

– RDP in to the server with an account that has local administrator privileges.
– From the Server Manager, Point to Tools, and then click on Services.
– Scroll down and select “User Access Logging” Service and then click on Stop this service.
– Right-click the “User Access Logging” service name and choose Properties, in the General tab, change the Startup type to Disabled, and then click OK.

You should not see any more of these errors. You may save the old Application and System Eventlogs and clear the eventlogs.

If you are using this service, then you may have to troubleshoot the same. One of the article for troubleshooting steps can be found from http://support2.microsoft.com/kb/2900773/ja
This is in Japanese language, but you can translate this easily to your language.

This is applicable on below versions of Windows Server
Windows Server 2012

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.

 

Top Known Issues with Microsoft SQL Server 2014 CU3

SQLServerF1

With release of each new version and new service packs of SQL Server, new issues or bugs will surface and Microsoft will work on fixing high priority bugs. Below are some of the top known issues which were identified and fixed in Microsoft SQL Server 2014 CU3.

– In Microsoft SQL Server 2014, if you are running a query which involves multiple table joins, and if any of the tables referenced in the query has Clustered Columnstore Index, then you may notice significant poor performance with the query because of missing Clustered Column Store Index segment elimination.

o Micorsoft has identified this issue and has released a fix for the same.
http://support.microsoft.com/kb/2984923/en-us
o Either Apply this fix if this is business critical issue, else apply latest available Cumulative Update or Service Pack.

– If you have defined a temporary variable and populated huge number of row into it, and then if you are using this temp variable in queries where you join this with other tables, then you may notice slow performance problems with the query.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– You may receive above error when you try to open memory-optimized table template from SQL Server Management Studio (SSMS) from a remote client machine and using SQL Server Authentication to connect to SSMS.
Failed to connect to Server ""
The target principal Name is incorrect. Cannot generate SSPI context

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– If a database which is part of AlwaysOn Availability Group and if this database consists of an In-Memory Table in new SQL Server and while you try to insert or update which affects multiple rows in one transaction on this table may lead to an “non-yielding scheduler” error. This will then result in the failover of the AlwaysOn Availability Group because the dump file generation takes a long time to complete.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– Access Violation exception and memory stack dump will be generated in SQL Server 2014, when you create a user-defined table and add some extended properties at the column level and then tried to query sys.extended_properties system table.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– You will receive below error when you have a FileTable or FILESTREAM in SQL Server 2014, and you have defined the MAXSIZE of FILESTREAM data to UNLIMITED and when the used size of FileTable when the FILESTREAM container size has reached 32 terabytes (TB) and then you tried to insert more data.
error 1105 Could not allocate space for object ‘‘ in database ‘’ because the ‘‘ filegroup is full.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– Log Reader Agent crashes by causing an access violation exception and memory stack dump during the initialization of the log reader agent in Transactional Replication.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– In SQL Server 2014, if you have a table that is partitioned and also has a clustered columnstore index and when you try to create an indexed view on this table, you will receive the error message: Internal Query Processor Error: The query processor could not obtain access to a required interface.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– By default “Auto Create Statistics” databases option is enabled on all databases. In SQL Server 2014, if you try to create a natively compiled stored procedure and if internally triggers the automatic creation of missing statistics, then a self-deadlock can occur which can significantly increases the compilation time.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– In SQL Server 2014, if you created a memory-optimized table variables outside of the natively compiled stored procedures, then a LCK_M_SCH_M (schema modify lock) is taken on the corresponding memory-optimized table, during destructing of the table variable, this any concurrent declaration of memory-optimized table variables outside of natively compiled stored procedures are blocked.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– In SQL Server 2014, If “UPDATE STATISTICS” is performed on a computed column that contains the “ISNULL” function then it will cause an access violation exception and generated a memory stack dump.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

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.

 
1 2 3