Home » Connectivity Issues

Connectivity Issues

sql server DBA 999

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'
Read the rest

sql server DBA 999

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
sql server DBA 999

Bringing Database back from Single User to Multi User mode with active connections

Bringing Database back from Single User mode to Multi User mode for Database with active connection:
Some times you will need to bring a database back from single user mode.
If we issue the below command,


USE [master]
GO
ALTER DATABASE [AdventureWorks] SET multi_USER
GO

It errors out:

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'AdventureWorks' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

For conditions like this:
Use the below query to find out the active connection to the database

select d.name, d.dbid, spid, login_time, nt_domain,
Read the rest

sql server DBA 999

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

sql server DBA 999

SQL Server TCP/IP Static and Dynamic Ports

SQL Server TCP/IP Static and Dynamic Ports must be configured/known by a DBA.

If Windows Firewall is active in a environment then all the ports closed except the ones which are given exceptions.
In some environments only the inbound connections are restricted,the outbound connections are not.
Exceptions to Firewall can either be given per Binaries or by Port.

To avoid connectivity issues with SQL Server, DBA should configure the Ports to Static where possible.
When a new SQL Server instance is installed if its Default instance then its given static port 1433.
But if the SQL Server instance is a named instance then it will give a Dynamic port to the SQL Server which may change when SQL Services are … Read the rest