Hi, some one help how to find inactive database list for 90 days

Database
Enthusiast

Hi, some one help how to find inactive database list for 90 days

Hi, some one help how to find inactive database list for 90 days and their permspace and avilable space in query.

Thanks,
shakthi

Accepted Solutions
Highlighted
Senior Apprentice

Re: Hi, some one help how to find inactive database list for 90 days

Hi,

 

A couple of ways to get this, but you will only get results if you have Query Logging turned on for the last 90 days (in your example).

 

1) This code can do this for you:

 select dspc.databasename
       ,max(dt1.lastaccesstimestamp) as lastaccesstimestamp  
       ,sum(dspc.maxperm)  as total_size
       ,sum(dspc.maxperm-dspc.currentperm) as available_space
from dbc.diskspacev as dspc
inner join (select databasename ,lastaccesstimestamp
                         from dbc.databasesv 
                         where cast(lastaccesstimestamp as date) <= date-90) as dt1
  on dspc.databasename = dt1.databasename
group by 1
order by 1;

...but you will only get any results if you have Query Logging (USECOUNT) turned on and you'll only get results for the databases that have the Query logging USECOUNT tracking feature enabled for them. To do this you need:

begin query logging with usecount on database-name;

2) If you have Query Logging turned on 'WITH OBJECTS' for (pretty much) all users on the system then you can use the DBQLObjTbl (either in DBC or a history database). That code using the DBC database is:

select dspc.databasename
       ,max(dt1.lastaccessdate) as lastaccessdate  
       ,sum(dspc.maxperm)  as total_size
       ,sum(dspc.maxperm-dspc.currentperm) as available_space
from dbc.diskspacev as dspc
inner join (select qobj.ObjectDatabaseName
                  ,max(cast(qlog.starttime as date)) as lastaccessdate
            from dbc.dbqlogtbl as qlog
           inner join dbc.dbqlobjtbl as qobj
          on qlog.queryid = qobj.queryid
            group by qobj.ObjectDatabaseName
            having lastaccessdate <= date-90) as dt1
on dspc.databasename = dt1.ObjectDatabaseName
group by 1
order by 1;

The Query Logging requirement is only if you're on TD14 or higher. Prior to that you can use the ObjectUseCountCollect feature and run the first query.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
2 REPLIES
Highlighted
Senior Apprentice

Re: Hi, some one help how to find inactive database list for 90 days

Hi,

 

A couple of ways to get this, but you will only get results if you have Query Logging turned on for the last 90 days (in your example).

 

1) This code can do this for you:

 select dspc.databasename
       ,max(dt1.lastaccesstimestamp) as lastaccesstimestamp  
       ,sum(dspc.maxperm)  as total_size
       ,sum(dspc.maxperm-dspc.currentperm) as available_space
from dbc.diskspacev as dspc
inner join (select databasename ,lastaccesstimestamp
                         from dbc.databasesv 
                         where cast(lastaccesstimestamp as date) <= date-90) as dt1
  on dspc.databasename = dt1.databasename
group by 1
order by 1;

...but you will only get any results if you have Query Logging (USECOUNT) turned on and you'll only get results for the databases that have the Query logging USECOUNT tracking feature enabled for them. To do this you need:

begin query logging with usecount on database-name;

2) If you have Query Logging turned on 'WITH OBJECTS' for (pretty much) all users on the system then you can use the DBQLObjTbl (either in DBC or a history database). That code using the DBC database is:

select dspc.databasename
       ,max(dt1.lastaccessdate) as lastaccessdate  
       ,sum(dspc.maxperm)  as total_size
       ,sum(dspc.maxperm-dspc.currentperm) as available_space
from dbc.diskspacev as dspc
inner join (select qobj.ObjectDatabaseName
                  ,max(cast(qlog.starttime as date)) as lastaccessdate
            from dbc.dbqlogtbl as qlog
           inner join dbc.dbqlobjtbl as qobj
          on qlog.queryid = qobj.queryid
            group by qobj.ObjectDatabaseName
            having lastaccessdate <= date-90) as dt1
on dspc.databasename = dt1.ObjectDatabaseName
group by 1
order by 1;

The Query Logging requirement is only if you're on TD14 or higher. Prior to that you can use the ObjectUseCountCollect feature and run the first query.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Hi, some one help how to find inactive database list for 90 days

Thanks you very much Dave for your help.