SQL Server Performance

How to find initial size of a DB

Discussion in 'General DBA Questions' started by gkrishn, Oct 24, 2005.

  1. gkrishn New Member

    Hi ,

    Suppose i have a database file (.MDF ) have size 6GB ,having auto growth set .Anyways to find how much was its initial size ?

    rajiv
  2. Luis Martin Moderator

    Initial size is 1Gb.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. satya Moderator

    When you use CREATE DATABASE statement it will be created using MODEL database sizes, which is by default 1MB or depending upon your specification.

    BOL clarifies
    SIZE

    Specifies the size of the file defined in the <filespec>. When a SIZE parameter is not supplied in the <filespec> for a primary file, SQL Server uses the size of the primary file in the model database. When a SIZE parameter is not specified in the <filespec> for a secondary or log file, SQL Server makes the file 1 MB.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. Luis Martin Moderator

    Hoop, misstake writing. Is 1MB.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  5. gkrishn New Member

    ok. Suppose i have created a database with size 2 GB , and it grown to 6GB .So is there anyway to find that it was created as 2GB database.?
    -Rajiv
  6. Luis Martin Moderator

    As far I know, no.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  7. Chappy New Member

    No I cant think of a way either.
    Why do you need this? Are you trying to get an idea of how the database has grown over time ?
  8. satya Moderator

    There will not be such information stored, look at Model database size and while creating if you haven't mentioned the size this db size will be equal to model.

    As Chappy referred why do you need this?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. Haywood New Member

    If you're looking for the historical growth and don't have anything in place to trap it already, you can use the backup_size column from table backupset in msdb and calculate your growth rates from there.
  10. gkrishn New Member

    I had found errors in my error log like 'TempDB full' ,So i just enabled autogrowth for my TempDB.next day i found my G: drive full and tempDB size was 15GB .

    So i thought i will shrink the file to a size that was set before.But i donno how much it was before.So just shrunk the file to 4096MB .
  11. Adriaan New Member

    Looks like (perhaps) excessive use of temporary tables. Set a maximum size for TempDB's data and log files.
  12. satya Moderator

    True and you must take control on TEMPDB and let system not drive by itself.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. Luis Martin Moderator

    Also, may be, some maintenance jobs could grow tempdb.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  14. gkrishn New Member

    So is it good to set max size? if i set max size somtime i am getting error "TempDB" full .If i enable autogrowth its goin up to 15GB and drive full. Does it means i have to configure TempDB with a HUGE size more than 15GB .Anyways to tackled this pbm?
  15. satya Moderator

    For temporary tables (# and ##), they are created in the tempdb database, and by thus on the disk. But as soon as the table goes out of scope or is droppped, the table is removed from the file.

    In SQL 2000, you can declare variables of type table, which can be the same. It depends on the size of data...

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=8652
    http://vyaskn.tripod.com/sql_server_administration_best_practices.htm

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  16. Vishnu Gupthan New Member

    ok. Suppose i have created a database with size 2 GB , and it grown to 6GB .So is there anyway to find that it was created as 2GB database.?
    -Rajiv
    -----------------------------------------------------------------------------------------------
    By VIshnu

    You can execute this query to fetch the sql server page details and in the property name called 'Min Size', you could find the initial size of the DB when it got created first will be there. (here 2GB)


    DBCC TRACEON(3604)
    GO

    DBCC PAGE('Your_DBName', 1, 0,3) WITH tableresults

Share This Page