SQL Server Last Database Access using Last Batch Date of sysprocesses or using SQL Server Audit Files and Database Audit Specifications

If you are working as a SQL Server Database Administrator (DBA), you may need a method or a t-sql script to identify the databases that are not used since a specific time or for a period of time.
Database administrators or database developers may need to get list of databases that are not used for a long time especially if administrators or programmers are new on the company and do not have enough information about the existing applications yet.
A SQL Server database administrator may want to take a database to offline state but can not since he does not know whether it is used or not.

I can suggest SQL Administrators and SQL Developers two methods to build the list of un-used databases.

First method is using the SQL Server audit files and database audit specifications. You can find sql codes required for implementing this method on you SQL Server 2008 instances.

Second method I can suggest is using two simple t-sql scripts executed by a sql server job that will help you identify databases that are in use or have not been used for a time period.
Here in these sql scripts I have taken a list of databases that are accessed in processes running on the SQL Server instance.
But on the current processes if a database record can not be found this does not actually point to that the related database is not used or will not be used by applications.
I mean if the application that accesses to the related database is used very rarely then it is possible that we can not find that process easily.

Use SQL Server Audit files and Database Audit Specifications to Get a List of Databases that are not Used

The best method for identifying last access to a database is running the SQL Server Auditing on the server level or on the database level on MS SQL Server 2008 databases.
For fetching database access information from auditing on SQLServer 2008, you should first define a server audit.
You can use the SQL Server Management Studio (SSMS) or you can use the CREATE SERVER AUDIT command syntax to create a SQL Server server audit file.

You can automate creating server audit files and database audit specification entries for every database in your MS SQL Server instance by using the un-documented stored procedure sp_MSForEachDB shown in the below sql codes.


EXEC sp_MSForEachDB '

USE [master];

CREATE SERVER AUDIT [Audit-?]

TO FILE 

(	FILEPATH = N''C:\SQLDatabases\''

  ,MAXSIZE = 0 MB

  ,MAX_ROLLOVER_FILES = 2147483647

  ,RESERVE_DISK_SPACE = OFF

)

WITH

(	QUEUE_DELAY = 1000

  ,ON_FAILURE = CONTINUE

);

ALTER SERVER AUDIT [Audit-?] WITH (STATE = ON)

'

Code

After creating and enabling the server audit files for use of each database, you can now create Database Audit Specification for each database under the security node of a database.
Or you can again automate the creation of the database audit specification records by using sp_MSForEachDB stored procedure.


EXEC sp_MSForEachDB '

USE [?];

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-?]

FOR SERVER AUDIT [Audit]

ADD (DELETE ON DATABASE::[?] BY [public]),

ADD (EXECUTE ON DATABASE::[?] BY [public]),

ADD (INSERT ON DATABASE::[?] BY [public]),

ADD (SELECT ON DATABASE::[?] BY [public]),

ADD (UPDATE ON DATABASE::[?] BY [public]);

ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-?]

WITH (STATE = ON)

'

Code

Of course you should also analyse these server and database level audit files after some time later you enabled them.
If you are looking only for the databases that are no longer used, then it is easy.
If there is no entry on the audit file for the related database then you can be sure that it is no longer used.

Get a List of Last Database Access in SQL Server using the Last Batch Date of sysprocesses

I can also suggest an other work-around to identify the databases that are not accessed any more.
Running a script at a timely schedule and restoring the results in a sql table will help database administrators to list databases that are not used any more.
After the sql job is executed for a long period of time to get enough data on the use of databases, we can easily say that a specific database is being used or is not used any more.

Sample script that prepares a database list that are accessed by current sql processes on the SQL Server instance.


WITH CTE
 
AS

(

SELECT 

  DB_NAME(dbid) dbname,

  rn = ROW_NUMBER() OVER (

    PARTITION BY DB_NAME(dbid) ORDER BY last_batch DESC

  )

FROM master..sysprocesses

WHERE spid > 50

)

SELECT * FROM CTE WHERE rn = 1 ORDER BY dbname

Code

A similar script example that gets a list of database that are not accessed by any of the current executing sql processes on the MS SQL Server instance.


SELECT

  d.name

FROM sys.databases d

LEFT JOIN master..sysprocesses p

  ON p.dbid = d.database_id

WHERE p.dbid IS NULL

ORDER BY d.name

Code

First we can create table for storing last access date information we gather from time to time by running the sql job we will create soon.


CREATE TABLE LastAccessDateToDatabase

(

  DatabaseName sysname,

  LastAccessDate datetime,

  InsertDate datetime DEFAULT GetDate()

)

Code

The first step in the job is adding new databases to the table.


INSERT INTO LastAccessDateToDatabase

(

  DatabaseName

)

SELECT

  DB_NAME(sys.databases.database_id)

FROM sys.databases

LEFT JOIN LastAccessDateToDatabase

  ON LastAccessDateToDatabase.DatabaseName = DB_NAME(sys.databases.database_id)

WHERE LastAccessDateToDatabase.DatabaseName IS NULL

Code

The second step of the SQL Server job will be updating databases that are already existing in the LastAccessDateToDatabase with the last_batch datetime information gathered from sysprocesses catalog view.


WITH CTE
 
AS

(

  SELECT

    DB_NAME(dbid) dbname,

    last_batch,

    rn = ROW_NUMBER() OVER (

      PARTITION BY DB_NAME(dbid) ORDER BY last_batch DESC

    )

  FROM master..sysprocesses

)

UPDATE LastAccessDateToDatabase

SET

  LastAccessDate = last_batch

FROM LastAccessDateToDatabase LADB

INNER JOIN CTE

  ON CTE.dbname = LADB.DatabaseName

WHERE CTE.rn = 1

Code

SQL Server Last Database Access using Last Batch Date of sysprocesses or using SQL Server Audit Files and Database Audit Specifications

Use SQL Server Audit files and Database Audit Specifications to Get a List of Databases that are not Used

Get a List of Last Database Access in SQL Server using the Last Batch Date of sysprocesses

More Tutorials on T-SQL sp_MSForEachTable Examples