Step1:
---Create a table to keep track of connectios:
USE AuditDB;
GO
CREATE TABLE [dbo].[TrackDBConnections]
(
ServerName VARCHAR(50) NOT NULL ,
DatabaseName VARCHAR(30) NOT NULL ,
NumberOfConnections INT NOT NULL ,
LoginTime DATETIME NOT NULL ,
LastBatch DATETIME NOT NULL ,
DateTimeRecordInsertd DATETIME NULL ,
Status VARCHAR(100) NULL ,
HostName VARCHAR(100) NULL ,
ProgramName VARCHAR(1000) NULL ,
NTusername VARCHAR(100) NULL ,
Loginame VARCHAR(100) NULL
)
ON [PRIMARY];
GO
Step2:--Main Step
---create a SP to insert the Data into the [TrackDBConnections] Table:
USE AuditDB
GO
CREATE PROCEDURE usp_TrackDBConnections
AS
BEGIN
/* The main purpose of the SP is to keep track of Connections which get connect to Databases
and decide on which database is being used or not being used by any applications and then make OFFLINE for fewdays, backup and then drop..
*/
SET NOCOUNT ON;
INSERT INTO [TrackDBConnections](ServerName, DatabaseName,LoginTime,LastBatch,DateTimeRecordInsertd,NumberOfConnections,Status,HostName,ProgramName,NTusername,Loginame)
SELECT @@ServerName AS SERVER ,
name ,
login_time ,
last_batch ,
GETDATE() AS DATE ,
COUNT(status) AS number_of_connections ,
status ,
hostname ,
program_name ,
nt_username ,
loginame
FROM sys.databases d
LEFT JOIN sysprocesses sp ON d.database_id = sp.dbid
WHERE database_id NOT BETWEEN 0 AND 4
AND loginame IS NOT NULL
GROUP BY name,status,login_time ,
last_batch,hostname ,
program_name ,
nt_username ,
loginame;
END
--Step3:-Create a Job to schedule to run every 10 Minutes
--Let it run for Few days/1 week and see the results with below query,Detailed SQL Server Connection Information
--Step4: CHECK results
----count
SELECT DatabaseName ,
COUNT(status) AS number_of_connections,
ProgramName
FROM TrackDBConnections
GROUP BY DatabaseName,ProgramName,Status
GO
--Details
SELECT * from TrackDBConnections
GO
