Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Posts
    64

    Question Unanswered: Getting a list of databases, their sizes, and last access times

    Hello!

    I'd like to be able to get the list of all databases, the amount of used spaces in them, and the last time they were accessed (we have the audit functionality setup).

    The following query works listing dbid and the last access times:
    Code:
    select dbid, max(eventtime) from sybsecurity..sysaudits_01
        group by dbid
    And the following gives the names and usage:

    Code:
    select name, sum(size - unreservedpgs) from sysdatabases, sysusages
        where sysusages.dbid = sysdatabases.dbid group by name
    However, when I try to combine them to get all results in one query:

    Code:
    select name, sum(size - unreservedpgs), max(eventtime)
        from sysdatabases, sysusages, sybsecurity..sysaudits_01
        where sysusages.dbid = sysdatabases.dbid
            and sysdatabases.name = sybsecurity..sysaudits_01.dbname
        group by name
    I get "Arithmetic overflow". Is the sysaudits-database "special"? Or are there other limitations on cross-database querying (I'm trying to access master and sysaudits_01 in a single query)?

    Is there a better way? Thanks!

  2. #2
    Join Date
    Jun 2006
    Posts
    16
    If that does not work, create a temp table, populate with two different separate sql and get the output. Remember db_name(id) can give you dbname without joining with sysdatabases

  3. #3
    Join Date
    Apr 2003
    Posts
    64
    Well, if I have to use multiple queries, I may as well do the join on the client side, without bothering with the temp table... Seems ugly, though.

    Did I find a Sybase bug?

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    The problem here is that your join condition is flawed
    There can be multiple entries in sysaudits_01 for a database
    causing it to sum sysusages for every entry in sysaudits_01 and overflowing.

    Edit: added code
    Code:
    select db_name(dbid), sum(size - unreservedpgs)
    , (select max(eventtime) from sybsecurity..sysaudits_01 
       where dbname=db_name(dbid)
      )
    from sysusages group by dbid
    Last edited by pdreyer; 06-15-06 at 09:40.

  5. #5
    Join Date
    Apr 2003
    Posts
    64

    Add working code.

    Thanks for the explanation.

    The query you posted is not quite right (yet), though, because the reported timestamp is now the same for all databases.

    Indeed, the subquery is independent of the main query and its `where' clause is always true :-) It returns the maximum eventtime of the entire sybsecurity..sysaudits_01, instead of one per database...

    The following seems to work. Can anyone think of a replacement, that would not have a subquery, however?

    Code:
    select name, sum(size - unreservedpgs),
         (select max(eventtime) from sybsecurity..sysaudits_01
           where dbid=sysusages.dbid)
    from sysusages, sysdatabases
        where sysusages.dbid = sysdatabases.dbid
        group by name, sysusages.dbid
    Or, using db_name() instead of explicitly referencing sysdatabases:

    Code:
    select db_name(dbid), sum(size - unreservedpgs),
         (select max(eventtime) from sybsecurity..sysaudits_01
           where dbid=sysusages.dbid)
    from sysusages group by dbid
    Last edited by teterin; 06-15-06 at 15:44.

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Must admit I haven’t tested the code (needed sysusages.dbid)
    Here is an alternative that you can test
    Code:
    select db_name(u.dbid), sum(u.size-u.unreservedpgs),max(a.eventtime)
    from master..sysusages u,
    (select dbid,eventtime=max(eventtime)
    from sybsecurity..sysaudits_01
    group by dbid ) a
    where u.dbid=a.dbid
    group by u.dbid

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    PS. Consider using this code to check your database size
    Code:
    select "dbname"=db_name(t1.dbid)
        ,"DataSize(MB)"=convert(int,sum(case when t1.segmap!=4 then @@maxpagesize/1024.*t1.size/1024 else null end))
        , "DataFree(MB)"=str(sum(case when t1.segmap!=4 then @@maxpagesize/1024.*curunreservedpgs(t1.dbid,t1.lstart, t1.unreservedpgs)/1024 else null end),11,3)
        ,"LogSize(MB)"=isnull(str(sum(case when t1.segmap=4 then @@maxpagesize/1024.*t1.size/1024 else null end),11) ,"on data dev")
        , "LogFree(MB)"=str(@@maxpagesize/1024.*lct_admin("logsegment_freepages", t1.dbid) /1024,11,3)
        from master..sysusages t1
        group by t1.dbid

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •