question

Ali 1 avatar image
Ali 1 asked

How to get a list of databases which are not in used

How to get a list of database in a server which are not in used or no DML operation has been performed on DB for selected time period?

administration
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered

What I've been doing recently is monitoring carefully what's going on (see David Bird's various articles on "SQLOverview" (http://www.sqlservercentral.com/Authors/Articles/David_Bird/120509/), and recording at regular intervals which databases are in use and by which applications.

After two weeks of monitoring, you should have a good idea about what is used and what isn't, and you can then start pruning.

Whatever you do, though, don't immediately drop databases! I generally put them into read-only mode for a while, then into dbo-only, then detach them. Take it slowly, and let people know what you're doing.

[edit: what I did on my holidays, sorry, what I did to monitor this stuff]

Create a table:

CREATE TABLE [dbo].[Database_Usage]
  (
     [Server]       [NVARCHAR](128) NOT NULL,
     [InstanceName] [NVARCHAR](128) NULL,
     [DatabaseName] [NVARCHAR](128) NOT NULL,
     [ProgramName]  [NVARCHAR](128) NULL,
     [UserCount]    [INT] NOT NULL,
     [TimeStamp]    [DATETIME] NOT NULL
  )
ON [PRIMARY]
ALTER TABLE [dbo].[Database_Usage] ADD CONSTRAINT [DF_Database_Usage_TimeStamp] DEFAULT (Getdate()) FOR [TimeStamp]

Populate this table with the following query:

INSERT INTO Database_Usage ([server], [instancename], [databasename], [programname], [usercount])
SELECT SERVERPROPERTY('machinename'), SERVERPROPERTY('instancename'), DB_NAME(dbid),
program_name, COUNT(*)
FROM sysprocesses
GROUP BY dbid, program_name

Run the above query every 10 minutes. (Scheduled tasks are your friends.) After a while, you should have a good idea of what software is accessing what databases on your server, and can start making decisions.

5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have gone through his article ,but I coudn't find anything which is related to my question . Report I need to generate is like a.Database Name b.DB size c.FileType d.LastUsed or Last Action on DB (for e.g any DML Opertion) Result: I have a server contains more than 200 database. List out those database which are not in used .
0 Likes 0 ·
Ali: what Thomas is suggesting is that you start to monitor for activity, and at the end of that time period you may have a better idea of what is/isn't used. There is no data to query to give you the magic answer.
0 Likes 0 ·
Sorry - didn't make that quite clear in my answer. Like I said, though, what I did was extend the functionality of the system discussed to do this monitoring. One snapshot every ten minutes - lots of data! I'll update my response with more detail.
0 Likes 0 ·
this is a good solution.
0 Likes 0 ·
@CirqueDeSQLeil - high praise indeed! Thanks.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

The only way to be 100% sure is to do what Thomas has suggested, set up monitoring on your server and collect data over time. However, you can query the cache to see which databases have had activity recently (meaning, since any activity was flushed out of cache). You do this using dynamic management views. I'd suggest using sys.dm_exec_query_stats along with sys.dm_sql_text to get a list of databases that have been accessed. You can then join this against the list of databases in the system to get databases without activity.

HOWEVER, there is a huge caveat to this. The cache can be highly volatile, depending on your system. So you can't count on this as a method to ensure there has been ZERO activity on a database. I'm just offering some alternatives. The best method, again, is what Thomas suggested.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - I got about half way through writing an answer like this, then got distracted by the kids :)
0 Likes 0 ·
+1 - pretty much sums it up
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.