Post reply

Monitore used databases

  • March 17, 2011 at 1:56 pm

    #99560

    Hello,

    I am trying to find out which databases are no longer used . How to run activity monitoring for all the databases

    Thank you

  • March 17, 2011 at 3:13 pm

    #1300251

    in SQL 2000(the forum you posted in), i think it's a little tougher;

    the easiest way to be to set up a trace on the server and check that after letting it run for a period of time.

    Before i found out about traces way back when in my SQL 2000 days, I think in the past I set suspected databases to AUTOCLOSE, and then went thru the Operating system's Event log to see if they ever got re-opened over a period of time; Anything after that I took offline and waited for someone to complain. Left them that way for months, because not every database was used very often.

    I know in SQL 2005 and above, you can check the indexes in sys.dm_db_index_usage_stats for last accessed, but that can be misleading since it's only since the server was last restarted.

    Aaron Bartrand's got a nice post about it that i used as a baseline to build my own version to check last accessed info :

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

    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!

  • March 22, 2011 at 1:05 pm

    #1302115

    Thank you, I am trying to upgrade to SQLServer 2005 and wanted to get rid off old databases before upgrading,so your sugesting put a trace on databases?

  • March 22, 2011 at 1:09 pm

    #1302118

    For sql 2000 a trace would be good for this purpose.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 22, 2011 at 1:32 pm

    #1302131

    Can you please help me to set up a trace, I never did it. Thank you

  • March 22, 2011 at 1:48 pm

    #1302146

    Is there way to monitor DTS packages and Replication jobs that are used?

Viewing 6 posts - 1 through 5 (of 5 total)

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