Find Unused databases

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