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?
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.
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.
No one has followed this question yet.