Building on my “is the database used?” article I developed this SQL-job as a light-weight audit to find unused databases and logins.
The job was scheduled to run every minute on every server, and had to work on SQL 2000, 2005, 2008r2 and 2012.
There were 4 steps
1) Initial Setup – Database Audit
--create table of database names
select [name] dbase_name, [dbid], cast(null as datetime) last_used
into [master].[dbo].[dbaudit]
from [master].[dbo].[sysdatabases]
where [dbid] > 4
2) Initial Setup – Login Audit
-- initial setup
CREATE TABLE loginAudit (
login_name VARCHAR(200), last_login_date DATETIME)
INSERT INTO loginAudit (login_name, last_login_date)
SELECT DISTINCT sl.name, NULL FROM syslogins sl
Steps 1 and 2 create the tables to hold the results, and interestingly carry on to the next step if they fail (IE: the tables already existed). I did it this way so I could just run it without any preparation.
3) Update Results – Database Audit
--update connections
update [master].[dbo].[dbaudit]
set last_used = getdate()
where [dbid] in
(select [dbid] from [master].[dbo].[sysprocesses])
4) Update Results – Login Audit
-- update logins
SELECT MAX(login_time) [login_time], loginame login_name INTO #loginTempTable
FROM master.dbo.sysprocesses
where loginame not in ('sa')
and loginame not like ' %'
GROUP BY loginame
UPDATE loginAudit
SET last_login_date = ltt.login_time
FROM #loginTempTable ltt
WHERE loginAudit.login_name = ltt.login_name
I wanted steps 3 and 4 to capture the current information without any table growth, so if my little job ran for years unnoticed the results-tables would not be huge.
BTW, I developed this on a candidate server from each of the 4 SQL versions, before scripting out the job on SQL server 2012 (“Drop and Create”).
FYI, I tested the resulting script (“CreateJob_DBAAudit.sql”) on the 4 servers again, and made some minor adjustments (so it would work more than once, and without errors on SQL-2000), before deploying to all 300+ production servers via SSMS Registered Servers.
Database Consultant 