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 : firstname.lastname@example.org
Thanks in advance.
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.
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.
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?
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.
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.
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?