Many times throughout my career, I have needed to complete tasks on databases that there was little to no information about. I often refer to these little guys as mystery databases. Whether it be migrations, decommissions, troubleshooting application issues or any other issues, it can sometimes be hard to figure out who or what is using one of these mystery databases. Even speaking to people you think may know (or should) know the answers can come up fruitless. So how do you handle that?
Yes, you can run one of the various scripts (such as sp_who or sp_whoisactive) to see who\what is currently connected, but how confident can you be that is the complete list? What if an application only connects periodically? If you have little to no information on the database, can you know for sure? And yes, you could shut it down and see what breaks and who screams, but is this the most effective way?
I got tired of seeing blank stares and hearing “I don’t know” from application owners and developers so I created a process that lets me track connection history. This allowed me to answer these questions on my own or at least have more information available to me when talking to other teams. What I have found since we started running this process, is that is one of the most widely used tools that my team uses. Especially after this process has been running for months and have that data available to me.
The process is just a proc that pulls a distinct list from master.dbo.sysprocesses based on Instance name, database name, login, host name, and program name and loads this data into a table. Each time it runs I have a counter column that exists that is increased by 1 each time a distinct connection is found and a timestamp column that is updated with the most recent time it was found. I run this proc via a SQL Agent job on all of my servers on a 3-minute interval. The task is lightweight so you can run it as often as you like and the table should stay relatively small.
Let’s take a look at what you need to do to set this up. For the sake of this article, I am going to include it in the master database, however, I have a user created DBA database that is created on all my servers that I normally use. I use this for all the objects that I need created to manage a server.
1. Create a table that you plan to log the data into.
USE [master] GO CREATE TABLE [dbo].[SQLConnections]( [SQLConnections_ID] [int] IDENTITY(1,1) NOT NULL, [Instance_Name] [nvarchar](50) NOT NULL, [Database_Name] [nvarchar](100) NULL, [loginame] [nvarchar](128) NULL, [hostname] [nvarchar](128) NULL, [program_name] [nvarchar](128) NULL, [LastLogin] [datetime] NULL, [ConnectionCount] [bigint] NULL, CONSTRAINT [PK_SQLConnections] PRIMARY KEY CLUSTERED ( [SQLConnections_ID] ASC ))
2. Create the proc
USE [master]
GO
CREATE PROC [dbo].[sp_LoadCurrentConnections]
AS
-- Create a Temp Table to hold the current connections
IF OBJECT_ID('tempdb..#SQLConnections') IS NOT NULL DROP TABLE #SQLConnections
CREATE TABLE #SQLConnections
(Instance_Name nvarchar(50),
Database_Name nvarchar(100),
loginame nvarchar(128),
hostname nvarchar(128),
program_name nvarchar(128),
LastLogin datetime,
ConnectionCount bigint)
-- Insert into DBAdb.dbo.SQLConnections
INSERT INTO #SQLConnections
(Instance_Name, Database_Name, loginame, hostname, program_name, LastLogin, ConnectionCount)
SELECT @@Servername as Instance_Name,
DB_NAME(dbid) as Database_Name,
loginame,
hostname,
program_name,
MAX(login_time) as LastLogin,
1 as [Count]
FROM sys.sysprocesses
GROUP BY DB_NAME(dbid), loginame, hostname, program_name
--- Update any existing data with latest login and add to count
UPDATE SC
SET SC.LastLogin = SCS.LastLogin,
SC.ConnectionCount = SC.ConnectionCount + 1
FROM #SQLConnections SCS
INNER JOIN master.dbo.SQLConnections SC ON SC.Instance_Name = SCS.Instance_Name
AND SC.Database_Name = SCS.Database_Name AND SC.loginame = SCS.loginame
AND SC.hostname = SCS.hostname AND SC.program_name = SCS.program_name
--- Insert any new rows into the table that do not exist
INSERT INTO master.dbo.SQLConnections
(Instance_Name, Database_Name, loginame, hostname, program_name, LastLogin, ConnectionCount)
Select SCS.Instance_Name, SCS.Database_Name, SCS.loginame, SCS.hostname, SCS.program_name, SCS.LastLogin, 1
FROM #SQLConnections SCS
LEFT JOIN master.dbo.SQLConnections SC ON SC.Instance_Name = SCS.Instance_Name
AND SC.Database_Name = SCS.Database_Name AND SC.loginame = SCS.loginame
AND SC.hostname = SCS.hostname AND SC.program_name = SCS.program_name
WHERE SC.Instance_Name is null
Note: You probably could use some of the other commonly used scripts that exist out there and just log the data to a table as well. This script is just what I use since its simple and accomplishes what I need for it to.
3. Create a SQL Agent job to execute the stored proc on the schedule of your choosing. (Note: I typically run it every 3 minutes)
After everything is setup and running
Once you have the process running, you have a historical record of what is connecting to your servers and an idea of how often it is connecting.
Here is an example output from a server that I manage (names have been updated to generic values) that will give an idea of the result set.
From this table, you can see what each connection being made to your databases are and how often they are connecting. There are several things you can do with this information and you may find that it comes in handy a lot more then expected. You will find that anytime you need to see what connects to a database, this will be your go to place.
SQL By Dustin 