Home » sql server faq

sql server faq

SQL Server- Unable to connect to SQL Server Error 26

Error Description:

SQL server error 26- Unable to connect to SQL Express Error: 26-Error Locating Server/Instance Specified

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: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

Error Details: this sql server error 26 error locating server instance message is often encountered when connection to a SQL Server is tried and don’t know where to start to solve the problem.

We get this sql server … Read the rest

SQL Server- Unable to connect to SQL Express Error: 26-Error Locating Server/Instance Specified

Error Description:

SQL server error 26- Unable to connect to SQL Express Error: 26-Error Locating Server/Instance Specified

Error Details: this sql server error 26 error locating server instance message is often encountered when connection to a SQL Server is tried and don’t know where to start to solve the problem.

We get this sql server error 26 error message only when we are trying to connect to a SQL Server named instance.

Every time we try to make a connection to SQL Server named instance, we will send a SSRP UDP packet to the server machine UDP port 1434. We need this step to know configuration information of the SQL instance, e.g., protocols enabled, TCP … Read the rest

SQL Server- a network-related or instance-specific error occurred while establishing a connection to sql server

SQL Server- a network-related or instance-specific error occurred while establishing a connection to sql server

Error Details or symptoms: Can not connect to a SQL server and you get a error a network-related or instance-specific error occurred while establishing a connection to sql server

This error is seen in SQL Express installation as its installed a named instance by default with name of instance as Servername/SQLExpress.

Some other Errors Similar:

provider: named pipes provider, error: 40 – could not open a connection to sql server which is covered here.

provider: sql network interfaces, error: 26 – error locating server/instance specified which is covered here.

Resolution:

If we are connecting from Windows PC/Client to Windows server/SQL Server (server with SQL Server … Read the rest

SQL Server error 40 could not open a connection to sql server 2016

SQL Server error 40 could not open a connection to sql server 2016

Error related are:
error 40 could not open a connection to sql server

named pipes provider could not open a connection to sql server 53

invoke sqlcmd provider named pipes provider error 40 could not open a connection to sql server

named pipes provider could not open a connection to sql server 5 linked server

I have covered this error in another post as well which is here.

Cause of Error:

This error occurs when the SQL Server can’t be found on Network which means either IP address is not reachable or that the TCP port number is either not open or is not the correct port … Read the rest

Microsoft SQL Server Error: SQL SERVER – Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ‘)’ or msg 102 level 15 state 1 line 3 incorrect syntax near ‘ ‘

Microsoft SQL Server Error: SQL SERVER – Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ‘)’ or msg 102 level 15 state 1 line 3 incorrect syntax near ‘ ‘

Details of Error:

This error message occurs if we use GETDATE() like function as a parameter to Stored Procedure or Function Statement. or the second error is pertaining to problem when inserting data can be solved by making the column not null and also can be made default to some value.

Actual error message:

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ‘)’.’

Resolution:

This problem can be solved by using a local variable for storing the value of Getdate() instead of passing GETDATE() … Read the rest

Microsoft SQL Server Error: SQL SERVER – Msg 8152, Level 16, State 14 – String or Binary Data Would be Truncated

Microsoft SQL Server Error: SQL SERVER – Msg 8152, Level 16, State 14 – String or Binary Data Would be Truncated

Details of Error:

String or binary data would be truncated. The statement has been terminated.

This error message below occurs when part data is been truncated while loading a table.

Actual error message:

Msg 8152, Level 16, State 14, Line 8
String or binary data would be truncated.
The statement has been terminated.

Resolution:

Either Shortened the data which is being truncated or increase length of the column on the Table or use below statements at the top and bottom of sql insert statement which is suppress the error

SET ANSI_WARNINGS OFF;
-- Insert TSQL here.
SET ANSI_WARNINGS ON;
Read the rest

Microsoft SQL Server Error: 53 or Microsoft SQL Server Error: 11001 or SQL Server error 53 and 40. SQL server error 53 and 17

Microsoft SQL Server Error: 53 or Microsoft SQL Server Error: 11001 or or SQL Server error 53 and 40. SQL server error 53 and 17

Details of Error:

Can not connect to SQL Server error which can be Error Number: 11001 or Error Number:53, Severity: 20, State: 0 or SQL Server error 53 and 40. SQL server error 53 and 17

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.

Connection failed
SQL State '01000'
SQL Server error 53
[microsoft][ODBC SQL Server Driver][DBNETLIB]Connection Open
Connection failed
SQL State '08001
SQL
Read the rest

Active Connections to SQL Server Database 2008, 2005 by Application or User

To Check Active Connections to SQL Server Database grouped by User

Below query is very helpful in finding the Total CPU usage, Waiting Time, Elapsed Time, Number of Reads, Number of Writes, connections grouped by Login name related which are active in SQL Server Database.

To check Active Connections to SQL Server Database grouped by Login name


SELECT SUM(s.cpu_time) AS CPU,
SUM(s.total_scheduled_time) AS WaitTime,
SUM(s.total_elapsed_time) AS ElapsedTime,
SUM(c.num_reads) AS Reads,
SUM(c.num_writes) AS Writes,
COUNT(1) AS Connections,
s.original_login_name AS login
FROM sys.dm_exec_connections AS c
LEFT OUTER JOIN sys.dm_exec_sessions AS s
ON s.session_id = c.session_id
GROUP BY s.original_login_name

Below query is very helpful in finding the Total CPU usage, Waiting Time, Elapsed Time, Number of Reads, Number of Writes, connections Grouped by

Read the rest

How to check Log shipping status in SQL Server 2008

How to check Log shipping status in SQL Server 2008:

SQL server recovery model has to be Full recovery mode for log shipping which allows log backups.

sql server database log backup is shiped and restore from one server or Primary to another server Secondary/destination server. Reports can be run from Secondary server for business needs. Using sql server nolock is recommended if dirty reads are allowed for reporting purposes.

This can be done by two methods:

1.By using the standard report on Instance level for Transaction log shipping status both on Primary and each secondary/destination servers individually.
On the Primary/source server, it shows when backup jobs succeeded.
On Secondary/Destination servers, it shows both when last copy job succeeded plus … Read the rest

ASYNC_NETWORK_IO Waits SQL Server 2005 2008 2012 2014 2016

ASYNC_NETWORK_IO Waits SQL Server 2005 2008 2012 2014 2016

ASYNC_NETWORK_IO Wait type in SQL 2005 2008 2012 2014 or 2016 are associated with either a Network Performance issues in the Environment or a the client application is not processing results quickly enough from SQL Server.

As always, if you are One of the DBA’s who check Top Waits Stats on SQL Servers to get a insight on what is SQL Server waiting on actually for this performance issues. Below few tips on troubleshooting ASYNC_NETWORK_IO Waits SQL Server

The Top Waits Stats rollup query is used to find the Top Waits SQL Server is waiting on from last restart of SQL Server.

The First thing when dealing with ASYNC_NETWORK_IO Wait types … Read the rest