Identifying SQL Server Port Number

This article provides several ways to identify the TCP/IP port number used by SQL Server.

Find the Running Port Number through Query

The following query provides a direct answer about TCP/IP port used by the SQL Server, as well as the SQL Server’s IP address.

SELECT DISTINCT local_net_address, local_tcp_port 
FROM sys.dm_exec_connections 
WHERE local_net_address IS NOT NULL;

Find the Running Port Number through xp_readerrorlog

The command xp_readerrorlog read the SQL Server logs, which consists of SQL Server boot log, to find the SQL Server listening port through keywords in the boot log.

USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO

Get the Running Port from the TCP/IP Properties

  1. Open the SQL Server Configuration Manager
  2. In the SQL Server Configuration Manager, under the SQL Server Network Configuration, select your SQL Server instance.
  3. After that, right click on TCP/IP and select Properties. You will find the SQL Server running port on the following screen.

 

How to: Find a Table on a SQL Server across all Databases

Some times it is difficult to find a table name from a SQL instance that hosted tremendous database.  “Which database I suppose to search for?” is the question that often pop-up in DBA’s mind and they hope the user can give them the database name as well.  Sadly speaking in most cases user do not know which database their application connect to.

To find a table in the SQL across all databases you can use undocumented stored procedure sp_MSForEachDB.

sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%your_table_name%'''

How to: Find Who Deleted the Database or Table

Found a very useful script from SQLServerCentral Forums that it will help you to easily identify who is the person that had deleted your database or table.

With cteObjectTypes AS
    (
    SELECT
        TSV.trace_event_id,
        TSV.subclass_name,
        TSV.subclass_value
    FROM
        sys.trace_subclass_values AS TSV JOIN
        sys.trace_columns AS TC ON
            TSV.trace_column_id = TC.trace_column_id 
    WHERE
        TC.[name] = 'ObjectType'
    ),
    cteEventSubClasses AS
    (
    SELECT
        TSV.trace_event_id,
        TSV.subclass_name,
        TSV.subclass_value
    FROM
        sys.trace_subclass_values AS TSV JOIN
        sys.trace_columns AS TC ON
            TSV.trace_column_id = TC.trace_column_id 
    WHERE
        TC.[name] = 'EventSubClass'
    )
SELECT
    TE.[name],
    I.ApplicationName,
    I.BigintData1,
    I.ClientProcessID,
    I.ColumnPermissions,
    I.DatabaseID,
    I.DatabaseName,
    I.DBUserName,
    I.Duration,
    I.EndTime,
    I.Error,
    I.EventSequence,
    Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
    I.FileName,
    I.HostName,
    I.IndexID,
    I.IntegerData,
    I.IsSystem,
    I.LineNumber,
    I.LoginName,
    I.LoginSid,
    I.NestLevel,
    I.NTDomainName,
    I.NTUserName,
    I.ObjectID,
    I.ObjectID2,
    I.ObjectName,
    Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,
    I.OwnerName,
    I.ParentName,
    I.Permissions,
    I.RequestID,
    I.RoleName,
    I.ServerName,
    I.SessionLoginName,
    I.Severity,
    I.SPID,
    I.StartTime,
    I.State,
    I.Success,
    I.TargetLoginName,
    I.TargetLoginSid,
    I.TargetUserName,
    I.TextData,
    I.TransactionID,
    I.Type,
    I.XactSequence
FROM
    sys.traces T CROSS Apply 
    sys.fn_trace_gettable((SELECT CONVERT(varchar(100), value)
             FROM sys.fn_trace_getinfo(default)
             WHERE property=2), default) I JOIN
    sys.trace_events AS TE ON 
        I.EventClass = TE.trace_event_id LEFT JOIN
    cteEventSubClasses AS ESC ON
        TE.trace_event_id = ESC.trace_event_id And
        I.EventSubClass = ESC.subclass_value LEFT JOIN
    cteObjectTypes AS OT ON 
        TE.trace_event_id = OT.trace_event_id AND
        I.ObjectType = OT.subclass_value
WHERE
    T.is_default = 1 AND
    TE.NAME = 'Object:Deleted'

Source from SQLServerCentral – Identify who deleted your database or table

How to: Hide SQL Server User Databases in SSMS

Hiding all user databases for all logins

Suppose you want to hide all databases for all logins. Generally we hide our databases for security purposes. We can run the below statements to hide all databases for all logins. The databases will then only be visible to sysadmin logins or owners of the database.

USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO

Once you run the above statement, you will not be able to see any databases in SQL Server Management Studio unless you are a sysadmin or your login is the owner of a database(s).

References

How to: Kill User Session

Let’s say you want to kill all running process by a particular user or account. The following query will help you to find all related SPID for all processes initiated by a particular user or account.

You may need to replace “USER_ACCOUNT” in the query with your target account name.

SELECT spid from master..sysprocesses
WHERE loginame = N'USER_ACCOUNT'

After you obtain the list of SPID, it’s time for you to reload your weapon, you can simply execute KILL command against those processes.

How to: Start SQL Server with Minimal Configuration

When you encounter configuration problem that prevent SQL Server from starting, you may need to start an instance using the minimal configuration option. This is the start up option -f. Starting an instance of SQL Server with minimal configuration automatically puts the server in single-user mode.

Kindly not what you can do when you start an instance of SQL Server in minimal configuration mode, as below:

  • Only a single user can connect, and the CHECKPOINT process is not executed.
  • Remote access and read-ahead are disabled.
  • Startup stored procedures do not run.

After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.

sqlservr.exe -c -f -s <Instance_Name>

You may want to know:
Starting SQL Server in Minimal Configuration
How to start SQL Server if you lost TEMPDB Data files?

How to: Identify Database Creation Date

Sometimes the database creation date will help you to examine whether any database within your SQL Server environment has been dropped and recreated or identify when the new database created. The following T-SQL statement will provide you a simple piece of information on that.

SELECT 
	name AS [DB Name], 
	dbid AS [DB ID]
	crdate AS [Created Date]
FROM 
	master..sysdatabases

Meanwhile, you might probably want to check out how to examine your table creation date and last modified date in order to help you dive deeper into your SQL Server environment.

How to: Get I/O Utilization by Database

This cool scripts obtain from Glenn Berry’s SQL Server Performance Scripts library to help you to find out the I/O utilization for each databases in your SQL Server instance. If you are looking for a script to shows you which database are the busiest one, like I do, in our database instance, this will the right script you need to grab and put in your toolbox.

-- Get I/O utilization by database (IO Usage By Database)
-- Helps determine which database is using the most I/O resources on the instance
-- Scripts adopted from SQL Server 2008 R2 Diagnostic Information Queries (February 2013).sql

SELECT 
	DB_NAME(database_id) AS [Database Name],
	CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM 
	sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY database_id;

How to: Sort SQL Results in Your Own Preferred Order

Classic sorting method allows you to sort table base on particular column either ascending or descending order. The following method allow you to customize the sorting result in your own preferred order, for instance sort a list of SQL Server in your environment based on server environment.

SELECT * FROM [SQL_ServerList]
ORDER BY
	(CASE ServerType
	WHEN 'PROD'	THEN 1
	WHEN 'QA'	THEN 2
	WHEN 'DEV'	THEN 3 END), 
	[Instance Name], [SQLServer Name]