If you ever have a need to monitor connections to your SQL server, and any related information about the connections such as database, logins, etc., there are some DMVs that can give you tons of information. Previously you might have used the sys.sysprocesses table to derive much of this information, but this is being deprecated in the most recent versions of SQL server.
Instead, you can collect valuable information from these DMVs:
In order to capture and retain connection information for my SQL server, I will create a small database and a table to hold some basic information. Of course you can alter the script to include more, less, or different data than what I am demonstrating below, to better fit your specific information needs.
I will create a database and a table, then insert data from two of the DMVs listed above.
— Create a database
CREATE DATABASE [Connections]
( NAME = N’Connections’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Connections.mdf’ ,
SIZE = 1024MB , FILEGROWTH = 512MB )
( NAME = N’Connections_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Connections_log.ldf’ ,
SIZE = 1024MB , FILEGROWTH = 512MB)
— Create table to hold Login info
CREATE TABLE [dbo].[LoginActivity]
host_name [nvarchar](128) NULL,
program_name [nvarchar](128) NULL,
login_name [nvarchar](128) NOT NULL,
client_net_address [nvarchar](48) NULL,
DatabaseName [nvarchar](128) NOT NULL,
login_time [datetime] NOT NULL,
status [nvarchar](30) NOT NULL,
date_time[datetime] NOT NULL,
) ON [PRIMARY]
If you need to retain or archive this connection information, you can create a database which will hold the information, or export the results to a spreadsheet or other file. Otherwise you can simply select the information from the DMV below if you only need to see current data.
INSERT INTO LoginActivity
— run the following select statement by itself to see connection info if you don’t want to save the output
d.name AS DatabaseName,
GETDATE() AS date_time
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
JOIN sys.databases d ON d.database_id = s.database_id
–where d.name = ‘ABCompany’ –can specify databases if needed
WHERE GETDATE() >= DATEADD(hh,-10, GETDATE()) –date range can be adjusted
After inserting the data into my table, I can see the current connections from the last 10 hours (as per my insert statement). On a production server, this list would be far greater.
SELECT * FROM LoginActivity
From the columns I have included in my table:
Host_name – will give you the name of the workstation connecting – shows NULL for internal sessions.
Program_name – tells you the name of the client program or application connecting.
Client_net_address – provides the host address of each client connecting
Login_name, DatabaseName, and login_time – self-explanatory.
date_time – is the current day and time the query is run
Status – gives the status of the session, which will be running, sleeping, dormant, or preconnect.
This information can also be output to a text or excel file if preferred.
Being able to see users or applications making connections to your SQL Server can be useful or necessary for many reasons. The steps outlined above provide a general guideline for deriving connection information that can be altered to fit your organizational needs.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!