September 20, 2024 at 11:15 am
Hi,
I'm working on cleanup activity of deleting databases that aren't in use or not used for a while.
I would like to do an automation to check this every once in 3 months and get the details on email or excel so I process further for cleanup. Have to validate on 100+ SQL servers.
Can someone help me how this can be achieved.
September 21, 2024 at 12:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 21, 2024 at 4:40 pm
First, I hope that you're taking a final full backup followed by a "Tail Log" backup to properly shut down a database prior to drop it.
As for determining if the database has been used or not, that's actually more difficult that you might imagine. All evidence of usage is wiped clean if the server is restarted OR the SQL Server Service is restarted for any reason (such as regularly scheduled Windows updates.
With that being understood, we need to accumulate the data in a permanent table and create a bit of code to populate it with. I'll leave the actual reporting up to you (you have to have some of the fun 😉 ).
And, just to say it again, I'm am not responsible for anything having to do with this code or how someone uses it. PERIOD. It does no deletes on its own and it's up to the user to determine if the data is appropriate for use and how to use it. PERIOD.
Details are in the comments.
/******************************************************************************
Purpose:
Collect the lastest usage dates for all databases except for master, model,
msdb, and tempdb and saves the dated entry for each database in the related
dbo.DbUsageHistory which, of course, must already exist.
Note: This has not been tested on databases that use "In Memory" or any
Columnstore tables and does not check for table-less proc usage.
It samples sys.dm_db_index_usage_stats for index usage, which relates
to the tables which relates to the databases.
-------------------------------------------------------------------------------
Dev Notes:
1. This script may be turned into a stored procedure or, if the INSERT clause
is removed, into an iTVF (inline Table Valued Function) or view.
-------------------------------------------------------------------------------
Usage:
Note that the author of this code is NOT responsible for any problems or
issues that occur by using this code in any way, shape, or fashion. PERIOD!
1. Create the history table in your "DBA" database using the following code:
CREATE TABLE dbo.DbUsageHistory
(-- Column names with underscores are
-- the same as from system tables.
SampleDateTime datetime NOT NULL
,database_id smallint NOT NULL
,last_user_seek datetime NULL
,last_user_scan datetime NULL
,last_user_lookup datetime NULL
,last_user_update datetime NULL
,MaxSampleUsageDate datetime NULL --Max of the 4 dates above
,CONSTRAINT PK_DbUsageHistory
PRIMARY KEY CLUSTERED (SampleDateTime,database_id)
)
;
2. Create a job to use the following code (or converted to stored proc, iTVF,
or View) to sample the database usage dates on a regular basis.
3. Create a report (code not inclued here) to get the maximum
MaxSampleUsageDate date for each DB.
Remember that this report is based on data that disappears (restarts) after
every server reboot or restart of the SQL Server service. It is recommended
that no action be taken base on the report until an adequate number of days
have passed to see if rarely used databases are used at all.
4. You may want to setup some form of "rolling delete" on the related history
table or do the occasional "TRUNCATE" to "Start Over" while remembering to
allow some good period of time (95 days or more to cover "once per quarter"
usage, IMHO) to pass.
-------------------------------------------------------------------------------
Revision History:
Rev 00 - 21 Sep 2024 - Jeff Moden
- Initial creation (based on previous code for indexes) and unit test.
- Ref:https://www.sqlservercentral.com/forums/topic/automate-reports-for-when-the-sql-database-was-last-used
******************************************************************************/ WITH cteUsed AS
(--==== Get the max usage dates for all used databases.
-- Will return ALL NULLs for any database that hasn't
-- used as of the sample date after the last restart.
SELECT SampleDateTime = GETDATE()
,db.database_id
,last_user_seek = MAX(usg.last_user_seek )
,last_user_scan = MAX(usg.last_user_scan )
,last_user_lookup = MAX(usg.last_user_lookup)
,last_user_update = MAX(usg.last_user_update)
FROM sys.dm_db_index_usage_stats usg
RIGHT JOIN sys.databases db
ON usg.database_id = db.database_id
WHERE db.database_id > 4 -- Exclude 4 main system databases
GROUP BY db.database_id
)
INSERT INTO dbo.DbUsageHistory
(
SampleDateTime
,database_id
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
,MaxSampleUsageDate
)
SELECT -- This gets the max of the 4 dates and
-- echoes the other dates for the INSERT
usd.SampleDateTime
,usd.database_id
,usd.last_user_seek
,usd.last_user_scan
,usd.last_user_lookup
,usd.last_user_update
,mx.MaxSampleUsageDate
FROM cteUsed usd
CROSS APPLY (-- Finds the max of the 4 dates for each row.
SELECT MAX(last.UsageDate)
FROM (VALUES
(last_user_seek)
,(last_user_scan)
,(last_user_lookup)
,(last_user_update)
)last(UsageDate)
)mx(MaxSampleUsageDate)
;
--===== Just to see what we have
SELECT * FROM dbo.DbUsageHistory
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply