SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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

More Tutorials on T-SQL sp_MSForEachTable Examples

sp_MSForEachTable Example T-SQL Code to Count all Rows in all Tables in MS SQL Server Database
sp_Msforeachdb Example : List All Database Files using sp_Msforeachdb Undocumented Stored Procedure
Create Same Stored Procedure on All Databases using sp_MSForEachDB T-SQL Example
MS SQL Server Execute Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable with Example T-SQL Codes
Listing All MS SQL Server Databases Using T-SQL
SQL Server Last Database Access using Last Batch Date of sysprocesses or using SQL Server Audit Files and Database Audit Specifications


SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.