Post reply

No. of Offline Databases

  • March 17, 2015 at 8:24 am

    #295144

    Hi there ..

    I am using this query to find out the database name and when the last time that offline database is been used. i tried to look by log but log not was never configured and hence i cant get that information from there.. i wrote a query but its resulting in by showing me all the online databases not the offline database..

    Can someone help me in this please .. Here is the query

    select @@servername AS Server_name, a.Name, a.database_id,a.state_desc,b.login_time, b.last_batch,b.[status],

    c.last_user_seek,

    c.last_user_scan,c.last_user_update

    from sys.databases a

    left join sysprocesses b

    on a.database_id = b.dbid

    join sys.dm_db_index_usage_stats c

    on a.database_id = c.database_id

    and a.database_id not between 1 and 4

    and c.last_user_scan < '2015-03-17'

    order by name

  • March 17, 2015 at 8:37 am

    #1784512

    There are certain events that cause index usage stats to be reset, and it looks as if taking a database offline is one of them. If you want to rely on index usage stats, I would recommend that you collect and store them regularly, perhaps every 30 minutes.

    John

  • March 17, 2015 at 8:40 am

    #1784514

    i just want to know when these databases used last time so ill take their backup and detached them thats why i am using the index usage..

    you think if i should use something else then i am open

  • March 17, 2015 at 8:45 am

    #1784517

    John suggested what you should do.

    John Mitchell-245523 (3/17/2015)

    If you want to rely on index usage stats, I would recommend that you collect and store them regularly, perhaps every 30 minutes.

    Set up something to record the stats for several months (make sure you include the year-end process), then you'll have good information about what gets used and what doesn't

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 17, 2015 at 8:45 am

    #1784518

    I'm not saying that you should necessarily use something else. I'm saying that it's possible for index usage stats to be cleared between the last time the database was used and when you run your query. That's why you should take a regular snapshot of the index usage stats if you want to rely on them.

    John

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

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