none
Size of real databases

    Question

  • Dear friends,

    where can I find information about real size of databases from Information Systems big companies?
    I read a lot of (only) theoretical information.
    Thanks a lot for answers,
    Anna

    Friday, November 08, 2013 8:02 PM

Answers

  • but I don't intersted in my own databases. I am intersted in huge databases from information systems (for ex. ERP) generally.

    Hello Anna,

    That differs also from customer to customer, from application to application. We have databases in size between a few 100 MB up to 10 TB, transaction tables between a few thousand rows up to 15 billion rows.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, November 09, 2013 9:43 AM
  • In my real world experience, database sizes vary considerably.  Large companies are more likely to have very large databases but even small companies may have large databases depending on application needs.  It is not uncommon to see multi-terabyte databases on the high-end but databases are more typically GBs in size.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, November 09, 2013 1:37 PM
  • I wasn't being specific.  OLTP databases will tend to be smaller in distributed systems, plus there are often many smaller departmental databases in larger companies.  Data warehouses tend to be much larger because these contain much historical data and are often include data from multiple source systems.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, November 09, 2013 9:32 PM

All replies

  • Hi,

    Try this code hope that can help

    SELECT 
        DB_NAME( dbid ) AS DatabaseName, 
        CAST( ( SUM( size ) * 8 ) / ( 1024.0 * 1024.0 ) AS decimal( 10, 2 ) ) AS DbSizeGb 
    FROM 
        sys.sysaltfiles 
    GROUP BY 
        DB_NAME( dbid )

    Best Regards

    Friday, November 08, 2013 8:30 PM
  • Thanks Khaled,

    but in my SSMS there are only small databases. I am intersted in size of huge databases from information systems. How many rows and what size (informatively of course)  they can have usually?
    Where can I find this information?
    Thanks,
    Anna


    • Edited by Anna1313 Friday, November 08, 2013 9:30 PM
    Friday, November 08, 2013 9:02 PM
  • Not sure what you need but I guess can provide you a query where you can get information about tables, size and rows of your database:

    SELECT 
        TAB.NAME AS TABLE_NAME,
        SCHM.NAME AS SCHEMA_NAME,
        PART.ROWS AS ROW_COUNT,
        SUM(ALOOC.TOTAL_PAGES) * 8 AS TOTAL_SPACE_IN_KB, 
        SUM(ALOOC.USED_PAGES) * 8 AS USED_SPACE_IN_KB, 
        (SUM(ALOOC.TOTAL_PAGES) - SUM(ALOOC.USED_PAGES)) * 8 AS NOT_USED_SPACE_IN_KB
    FROM 
        SYS.TABLES TAB
    INNER JOIN      
        SYS.INDEXES IND ON TAB.OBJECT_ID = IND.OBJECT_ID
    INNER JOIN 
        SYS.PARTITIONS PART ON IND.OBJECT_ID = PART.OBJECT_ID AND IND.INDEX_ID = PART.INDEX_ID
    INNER JOIN 
        SYS.ALLOCATION_UNITS ALOOC ON PART.PARTITION_ID = ALOOC.CONTAINER_ID
    LEFT OUTER JOIN 
        SYS.SCHEMAS SCHM ON TAB.SCHEMA_ID = SCHM.SCHEMA_ID
    
    GROUP BY 
        TAB.NAME, SCHM.NAME, PART.ROWS
    ORDER BY 
        TAB.NAME
    

    The query is very common so you can use this at any time.

    Another piece of information is the database I work on is little over 250 GB(which is nothing comparing to 20 TB, in Oracle though) in whole size and some tables contains billions of rows.

    Friday, November 08, 2013 9:27 PM
  • Thanks Taherul I,

    but I don't intersted in my own databases. I am intersted in huge databases from information systems (for ex. ERP) generally.
    Anna

    Friday, November 08, 2013 9:39 PM
  • but I don't intersted in my own databases. I am intersted in huge databases from information systems (for ex. ERP) generally.

    Hello Anna,

    That differs also from customer to customer, from application to application. We have databases in size between a few 100 MB up to 10 TB, transaction tables between a few thousand rows up to 15 billion rows.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, November 09, 2013 9:43 AM
  • In my real world experience, database sizes vary considerably.  Large companies are more likely to have very large databases but even small companies may have large databases depending on application needs.  It is not uncommon to see multi-terabyte databases on the high-end but databases are more typically GBs in size.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, November 09, 2013 1:37 PM
  • Dear friends,

    thanks for information.  Did you say about size of centralized databases  or distributed databases?

    Anna

    Saturday, November 09, 2013 7:45 PM
  • I wasn't being specific.  OLTP databases will tend to be smaller in distributed systems, plus there are often many smaller departmental databases in larger companies.  Data warehouses tend to be much larger because these contain much historical data and are often include data from multiple source systems.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, November 09, 2013 9:32 PM