Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2006
    Posts
    11

    Red face Unanswered: how to optimize database size in SQL server 2000

    I designed a database for daily reporting system. It involves mainly INSERT operations and data retrieval. The problem here is the size of database is growing rapidly beyond the estimation. I changed the datatypes from int to tinyint, even after that i didn't find any difference in size of the database. Please suggest me solution by which i can optimize the database size also suggest me some tips by which i can estimate the database size. Please respond at the earliest possible.
    My mail ID : akbar.t@tcs.com
    Thanks in advance.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    if you are holding huge volume of data the size is bound to grow. cant be avoided.

    however, you can limit the growth of log file. for this either you need to backup regularly or do some compromise with backup principals. can also try "shrink database", though not recommended that way.

    find what is bothering you the datafile size or the logfile size.

  3. #3
    Join Date
    Sep 2006
    Posts
    11
    actually the main table 'DAILY_TRANSACT' contains 3 columns
    HEAD CODE - INT
    PARAM CODE - INT
    PARAM VALUE - INT

    For each transaction 83 rows will be inserted. per day 33 transactions.
    But 25 MB is getting over for these transactions. I dont know why? Will u please suggest me.

  4. #4
    Join Date
    Sep 2006
    Posts
    11
    For each transaction 83 rows will be inserted. per day 33 transactions.
    But 25 MB is getting over for these transactions in one month. I dont know why? Will u please suggest me.

  5. #5
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    the row length and the number of transactions that u r mentioning is really not matching the size reported. unless there is a mistake somewhere.

    right click on the DB from EM and select properties. check the "SpaceAllocated(MB)" for both the tabs "data file" and "transaction log". if it is the transaction log that is taking the space then by changing the log recovery mode u can reduce the size to as low as 5-10% of current size. if it is the data file, u need to understand what is there in your data.

    check http://msdn2.microsoft.com/en-us/library/ms175991.aspx

  6. #6
    Join Date
    Sep 2006
    Posts
    11
    log ricovery mode is 'simple'. Am i need to change it to 'bulk logged' or 'full' mode.
    Previously each row was occupying 12 bytes. Now i changed the datatypes to tinyint, tinyint, smallint. so each row is occupying 4 bytes. As a result the database size decreased from 37 MB to 24 MB . But according to our calculations the database size should come to 37/3 = 13 MB (because the row size decreased by 3 times). But why it is occupying 24 MB?
    Even I didn't find any change in the database size, after running the following command dbcc shrinkfile (1,13,notruncate) . what else i can do?

  7. #7
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    please do not expect the database size to be only dependent on this particular table. there could be other objects and there r a lot of overheads that contributes to the actual database size. also u can check the Fill-Factor of the clustered index (if any) in that table. that might be a major factor of size.

  8. #8
    Join Date
    Sep 2006
    Posts
    11
    But i didn't create any index in my table.
    when i run sp_spaceused , it is showing following results.
    name - rows - reserved - data - index_size - unused
    RCH_T_DAILY_TRANSACT - 397741 - 12040 KB - 12024 KB - 8 KB - 8 KB
    so 12024 KB/ 397741 rows = 31 bytes/row . But actually it is 12 bytes + 1 byte + 1 byte + 2 bytes = 16 bytes/row. Cant able to understand the extra occupancy of the size/row.

  9. #9
    Join Date
    Sep 2006
    Posts
    11
    how to minimise reserved space?
    Will it give that space back to the OS?

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How big is this database?

  11. #11
    Join Date
    Sep 2006
    Posts
    11
    It has totally 4 master tables and 1 dynamic table. It involves mainly INSERT operations and data retrieval. Remaining tables are occupying less than 3 MB.

  12. #12
    Join Date
    Sep 2006
    Posts
    11
    i.e. The 4 Master tables are occupying less than 3 MB.

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    3MB is a very small database in the SQL Server world. Is it really worth the time to try to shrink it further than 3MB?

  14. #14
    Join Date
    Sep 2006
    Posts
    11
    Totally 5 tables are there. Table1 (this is the main table where we are inserting the data) has the size more than 30 MB and remaining 4 master tables has 3 MB.

  15. #15
    Join Date
    Sep 2006
    Posts
    11
    Hi upalsen, i tried dbcc shrinkfile (1,10,notruncate) and dbcc shrinkfile (2,1,notruncate) . First command is not giving any improvement in the data file size. How to make reserved space for a table less?

Posting Permissions

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