Find unused databases on the server

Hi,
I have to find all databases on the server that haven't been used for last 3
month.
Is there an easy way to get this list ?
Programmer
This is hard to ascertain. Personally, I've set up a job to poll
the sysprocesses virtual table and to log into an audit table
I've built the database name and whether anyone has connected.
Someone else in another thread mentioned setting up autoclose
on a database and checking the SQL Server error log for when
the db opens. I'm little mixed on that because any queries issued
to the database will lag as the database is mounted.
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer
|||Backup first
Then delete the db's and wait to see who complains
Jeff
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer
|||A bit less drastic then the previous post, you could always put the db(s) in
single user mode. You could even put the autoclose options to those DBs and
take a look at your logs (keep in mind that the DBs will open whenever you
back them up or browse the list of DBs in EM) but you might have an idea if
the dbs are used or not.
Sasan Saidi
"Jeff Dillon" wrote:

> Backup first
> Then delete the db's and wait to see who complains
> Jeff
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> 3
>
>
|||You could create an autostart proc that begins a trace to do limited small
audit tracing. Capture just Audit Login and Audit Logout events and note the
dbid connected to. Spool these to a table.
Another, if you are regularly backing up the databases, you could examine
the msdb.dbo.backupset table and note the LSN increase between successive
backups. If the values are small, then there is not much activity going on
in the system. The LSN values are updated whenever transactions occur to the
database. No transactions, very little increase in LSN between backups.
Now, there are system processes that do transactions; so, the LSNs will
unlikely be the same. However, they should be much closer together than an
active database would be.
Sincerely,
Anthony Thomas
"Sasan Saidi" wrote:
[vbcol=seagreen]
> A bit less drastic then the previous post, you could always put the db(s) in
> single user mode. You could even put the autoclose options to those DBs and
> take a look at your logs (keep in mind that the DBs will open whenever you
> back them up or browse the list of DBs in EM) but you might have an idea if
> the dbs are used or not.
> Sasan Saidi
> "Jeff Dillon" wrote: