How to find out what databases have not being used for a year

Question

How to find out what databases have not being used for a year

New DBA

Old Hand

Points: 375
More actions
March 18, 2016 at 9:11 am

#305746

Hello,
I started working in a company where we have 6 production servers with a lot of databases on each server.
Is there a way to figure out what databases have not being used for a year, so we could take them offline.
Thank you

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

you can infer the last accessed time of every database since the server was last rebooted base din the index stats; if you've got something regularly rebooting your server, you'll have to add some sort of audit yourself.

i use this query:

--based on the ideas from

--http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

;WITH AllDatabases

AS

(

SELECT database_id,name AS TheDatabase from master.sys.databases WHERE name NOT IN('master','tempdb','model','msdb')

),

myCTE

AS

(

SELECT

AllDatabases.TheDatabase,

statz.last_user_seek,

statz.last_user_scan,

statz.last_user_lookup,

statz.last_user_update

FROM AllDatabases

LEFT OUTER JOIN sys.dm_db_index_usage_stats statz

ON AllDatabases.database_id = statz.database_id

)

SELECT

ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),

x.TheDatabase,

MAX(x.last_read) AS last_read,

MAX(x.last_write) AS last_write

FROM

(

SELECT TheDatabase,last_user_seek AS last_read, NULL AS last_write FROM myCTE

UNION ALL

SELECT TheDatabase,last_user_scan, NULL FROM myCTE

UNION ALL

SELECT TheDatabase,last_user_lookup, NULL FROM myCTE

UNION ALL

SELECT TheDatabase,NULL, last_user_update FROM myCTE

) AS x

GROUP BY TheDatabase

ORDER BY TheDatabase

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

I haven't met a read-only database, so you could probably compare backups.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eric M Russell SSC Guru Points: 125521 More actions · Answer 3

Once you've identified what you believe to be a list of databases no longer used, then set the database option 'Restrict Access' for each to RESTRICTED_USER mode (default is MULTI_USER), which will block access to any user except SYSADMIN. Another option is to take each of them OFFLINE. Keep them this way for at least 45 days before dropping, so they can be easily made available again if someone misses it. Also confirm you have a backups.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

Luis Cazares (3/18/2016)
I haven't met a read-only database, so you could probably compare backups.

Easy enough, if differential backups are consistently 0 in content and no connections are made to the database then drop the bugger:-D

On more serious note, using the index usage statistics doesn't really cut it as there are quite few holes to fall through such as heap tables, that is no index to use, no usage recorded, no one said that those databases are well structured/designed!

😎

Eric M Russell SSC Guru Points: 125521 More actions · Answer 5

Eirikur Eiriksson (3/18/2016)
Luis Cazares (3/18/2016)
I haven't met a read-only database, so you could probably compare backups.
Easy enough, if differential backups are consistently 0 in content and no connections are made to the database then drop the bugger:-D
On more serious note, using the index usage statistics doesn't really cut it as there are quite few holes to fall through such as heap tables, that is no index to use, no usage recorded, no one said that those databases are well structured/designed!
😎

Even if a database is read-only, it can still be used, even heavily used.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 6

Eric M Russell (3/18/2016)
Eirikur Eiriksson (3/18/2016)
Luis Cazares (3/18/2016)
I haven't met a read-only database, so you could probably compare backups.
Easy enough, if differential backups are consistently 0 in content and no connections are made to the database then drop the bugger:-D
On more serious note, using the index usage statistics doesn't really cut it as there are quite few holes to fall through such as heap tables, that is no index to use, no usage recorded, no one said that those databases are well structured/designed!
😎
Even if a database is read-only, it can still be used, even heavily used.

You are absolutely right, seen this happen more than once:Whistling:

😎

New DBA Old Hand Points: 375 More actions · Answer 7

Thank you, SSChampion.

But I cannot run the query because do not have permission on sys.dm_db_index_usage_stats .

This code below does not help

USE master;

GO

GRANT VIEW SERVER STATE TO [test1]

GO

Alexander Suprun SSCertifiable Points: 6233 More actions · Answer 8

New DBA (3/18/2016)
Thank you, SSChampion.
But I cannot run the query because do not have permission on sys.dm_db_index_usage_stats .
This code below does not help
USE master;
GO
GRANT VIEW SERVER STATE TO [test1]
GO

Is it because you are not user "test1" ? 🙂

Alex Suprun

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 9

Check out the following article maybe this will help you out: https://www.mssqltips.com/sqlservertip/3171/identify-sql-server-databases-that-are-no-longer-in-use/[/url]

New DBA Old Hand Points: 375 More actions · Answer 10

I am not user 'test1'. I put it just for example. Of cause I used the real user name 🙂

New DBA Old Hand Points: 375 More actions · Answer 11

I am not user 'test1'. I put it just for example. Of cause I used the real user name 🙂

New DBA Old Hand Points: 375 More actions · Answer 12

New DBA

Old Hand

Points: 375

March 21, 2016 at 6:49 am

#1866430

Thank you, great article, will review.

New DBA Old Hand Points: 375 More actions · Answer 13

Based on the article, I created table

Create Table dbo.DatabaseAudit

(

ServerNamevarchar(255) Not Null

,DBNamevarchar(255) Not Null

,LoginTimedatetime Not Null

,LastBatchdatetime Not Null

,[Status]varchar(55) Not Null

,HostNamevarchar(255) Not Null

,ProgramNamevarchar(255) Not Null

,NTUserNamevarchar(255) Not Null

,LogiNamevarchar(255) Not Null

,AuditDatedatetime Not Null

Constraint DatabaseAudit_DF_AuditDate Default (Getdate())

)

On [Primary]

Then created SP that populates this table

Create Procedure dbo.DatabaseAudit_Insert

As

Begin

Set Nocount On;

Insert Into dbo.DatabaseAudit

(

ServerName

,DBName

,LoginTime

,LastBatch

,[Status]

,HostName

,ProgramName

,NTUserName

,LogiName

)

Select

@@ServerName

,Name

,Login_Time

,Last_Batch

,[Status]

,Coalesce(HostName, '')

,Coalesce(Program_Name, '')

,Coalesce(NT_UserName, '')

,LogiName

From

sys.databases d

Left Join

master.dbo.sysprocesses sp On (d.database_id = sp.dbid)

Where

database_id Not Between 1 and 4 /* Exclude system databases */

And LogiName Is Not Null

End

Should SP be running in Master DB or in any DB? I have it now in Database1 (example). Also table is in Database1. I created job and it's running every 10 minutes. Why I have rows inserted by SP into my table only with DBName "Database1"? I am querying other databases in SSMS and expect to see those DB Names in my table. This SP using master.dbo.sysprocesses table, no matter in what database I am running it, I think it should return data about all databases that get queried. Why I get only info about Database1?

New DBA Old Hand Points: 375 More actions · Answer 14

Based on the article, I created table

Create Table dbo.DatabaseAudit

(

ServerNamevarchar(255) Not Null

,DBNamevarchar(255) Not Null

,LoginTimedatetime Not Null

,LastBatchdatetime Not Null

,[Status]varchar(55) Not Null

,HostNamevarchar(255) Not Null

,ProgramNamevarchar(255) Not Null

,NTUserNamevarchar(255) Not Null

,LogiNamevarchar(255) Not Null

,AuditDatedatetime Not Null

Constraint DatabaseAudit_DF_AuditDate Default (Getdate())

)

On [Primary]

Then created SP that populates this table

Create Procedure dbo.DatabaseAudit_Insert

As

Begin

Set Nocount On;

Insert Into dbo.DatabaseAudit

(

ServerName

,DBName

,LoginTime

,LastBatch

,[Status]

,HostName

,ProgramName

,NTUserName

,LogiName

)

Select

@@ServerName

,Name

,Login_Time

,Last_Batch

,[Status]

,Coalesce(HostName, '')

,Coalesce(Program_Name, '')

,Coalesce(NT_UserName, '')

,LogiName

From

sys.databases d

Left Join

master.dbo.sysprocesses sp On (d.database_id = sp.dbid)

Where

database_id Not Between 1 and 4 /* Exclude system databases */

And LogiName Is Not Null

End

Should SP be running in Master DB or in any DB? I have it now in Database1 (example). Also table is in Database1. I created job and it's running every 10 minutes. Why I have rows inserted by SP into my table only with DBName "Database1"? I am querying other databases in SSMS and expect to see those DB Names in my table. This SP using master.dbo.sysprocesses table, no matter in what database I am running it, I think it should return data about all databases that get queried. Why I get only info about Database1?