June 4, 2013 at 1:30 pm
I have same database across 2 different servers. One of them has it (mdf file) as 200GB and another one has it has 160GB. The one with 200GB is simple recovery model and another one with 160GB is Full with log backup taken every 2 hours. I am wondering why there is so much difference in size when the data is essentially the same. Now, I remember one time one of my team member did index rebuild on the one with 200GB db server. I am not sure if that has to do anything with its growth. What can I do to bring the size down on 200GB db?
June 4, 2013 at 3:09 pm
You are checking the total size, and it is different because files for these databases have some empty spaces. To check used space, you can use FILEPROPERTY(f.name, 'spaceused') for each file.
To equalize databases by size, you can shrink files for the largest one.
June 4, 2013 at 3:20 pm
what does it return? is it MB? The one with 200GB returns 23866968 and the other one is returning 19995352
June 4, 2013 at 3:30 pm
It returns in 8k pages. To get it in MB, devide by 128.
I see that number of pages are dirrefent. It is possible that one of them has tables, indexes that the other does not have. If this is not the case, compare fillfactor for indexes accross databases and space and number of records occupied by each table.
June 5, 2013 at 6:23 pm
Thanks. Also noticed its increasing about 1GB per day; which is not realistic. What should I check for?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply