How to get SQL port number using xp_readererrorlog

Question: Without going into the SQL Server Configuration manager via the GUI is there a command oriented method to extract the port number SQL Server is listening on? Answer: There are a few different methods to extract the port number without going into the Configuration interface. Method 1 - use xp_readererrorlog When SQL Server starts up it goes through a number of checks and records these in the SQL Server Error log files. one of the checks relates to ports SQL Server is listening on. This is an example to extract the port number . The first example is for... Read more →

Astronauts are taught this six-stage decision making process

Being accepted into an astronaut training program is one of the toughest entry processes in the professional world. As part of the entry acceptance process - potential astronaut candidates are subjected to constant testing to expose any weaknesses in pattern recognition, visual memory , working memory and so forth. As part of the astronaut training at the European Space Agency - astronauts are taught a six-stage decision making technique - with an acronym of FORDEC FORDEC stands for: F = Facts O = Options R = Risks D = Decision E = Execute C = Check Seems simple - but... Read more →

Connection failed - SQL Server Error 772 - TCPIP Socket

Question: I've upgraded an application with a built - in Database API . When attempting to establish a SQL Server database connection this error appears - Connection failed - SQL Server Error 772 - TCPIP Socket Upon investigation the application was using the native drivers attempting to connect to a SQL Server 2016 \ Windows 2016 . As part of the testing I downloaded the ODBC 13.1 SQL Server drivers - independent of the application and tested a DSN connection to the same SQL Server - and it connected OK. I then created a DSN with native drivers and the... Read more →

TLS , SQL Server and powershell cmdlet - Get-TLSCipherSuite

Question : I have an issue from a developer about an error on the app which may be related to TLS version support by SQL Server From what I can see this could be related to a TLSV1.0 version issue. Does SQL2016 support TLSV1.0 ? how can I view which cipher suites are available on both the server hosting SQL Server and on the desktop connecting to SQL Server Answer: Microsoft SQL Server 2016 supports TLS 1.1 and TLS 1.2 . SQL Server 2016 and SQL Server 2017 on Windows versions ship with TLS 1.0 to TLS 1.2 support. You... Read more →

Msg 9772, Level 16, State 1 - The Service Broker in database "xxxx" cannot be enabled because there is already an enabled Service Broker with the same ID.

Question: I'm attempting to execute the following ALTER DATABASE statement to enable BROKER on a database we've just restored. ALTER DATABASE [xxxxx] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE and getting this SQL error message Msg 9772, Level 16, State 1, Line 5 The Service Broker in database "xxxxx" cannot be enabled because there is already an enabled Service Broker with the same ID. How can I fix this problem and progress with enabling Service Broker? I've restored other databases on the same sql server without a problem . The database is restored successfully and connections are all OK Answer:This error occurs... Read more →

Query VMWare vCenter VCDB database for guest power state = OFF

Question: I want to identify virtual machines which are powered off. What query can I run which will return the full list of virtual machines within a VMWare vCenter database where the power state = OFF. Answer: It is possible to return the recordset by querying the database. Find a sample query executed on a vcDB database. The query includes some extra information , which you may find useful . The key column is the POWER_STATE column in the vpxv_vms table. There are different potential power states for a virtual machine . Possible values are off, on, and suspend SELECT... Read more →

How to find the local tcp port used on SQL Server

Question: How can I find the tcp port number used by a session ? I want to use a SQL Query to find this information as I don't have access to the cmdline. Answer: One method to use from within SQL Server is the sys.dm_exec_connections DMV . This SQL Server DMV returns various details about the current connections to a SQL Server Instance. This query returns connections information for all existing connections. One of the columns is the "local_tcp_port" , and when the net_transport is TCP - there will be a value in the "local_tcp_port" SELECT suser_sname() as username ,local_tcp_port... Read more →