find out database useage over time period

I have a query I am using

SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'databasename')
AND last_user_update >= '2011-02-14 14:54:00'
order by last_user_update asc

I have about 200 databases and im trying to find out which ones are actively being used.
first of all, please confirm that the last_user_update field is wiped clean during a sql server restart....  does this refer to total server reboot or just when the instance is restarted.

also, tough question, but is there a way to automate this for every database and to have it populate a an excel spreadsheet when I run it.  that way I will keep track between reboots...  

I would like the data to contain
database name and the first result of the above query.  and have it run once a week.  and populate a txt or csv file


I know almost an impossible question.  if someone has a better way to figure out my problem... please advise

Thanks a bunch
jamesmetcalf74Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
>> please confirm that the last_user_update field is wiped clean during a sql server restart <<

Confirmed.  If the SQL instance stops, the view is cleared; it does not require a full server reboot.


>> is there a way to automate this for every database and to have it populate a an excel spreadsheet when I run it <<

Yes.  Easiest would probably be to create an SSIS package that does this and schedule it to run when you want it to.  

But, to me, it seems much easier, and vastly more useful, to instead add query results to your own SQL table of this data rather than going to a spreadsheet/separate file.  Then all you'd need is a SQL job that copied the existing view rows to your table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.