I currently have a database that is 20GB in size.
I've run a few scripts which show on each tables size (and other incredibly useful information such as index stuff) and the biggest table is 1.1 million records which takes up 150MB of data. We have less than 50 tables most of which take up less than 1MB of data.
After looking at the size of each table I don't understand why the database shouldn't be 1GB in size after a shrink. The amount of available free space that SqlServer (2005) reports is 0%. The log mode is set to simple. At this point my main concern is I feel like I have 19GB of unaccounted for used space. Is there something else I should look at?
Normally I wouldn't care and would make this a passive research project except this particular situation calls for us to do a backup and restore on a weekly basis to put a copy on a satellite (which has no internet, so it must be done manually). I'd much rather copy 1GB (or even if it were down to 5GB!) than 20GB of data each week.
sp_spaceused reports the following:
Navigator-Production 19184.56 MB 3.02 MB
And the second part of it:
19640872 KB 19512112 KB 108184 KB 20576 KB
while I've found a few other scripts (such as the one from two of the server database size questions here, they all report the same information either found above or below).
The script I am using is from SqlTeam. Here is the header info:
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@<email removed>
* v1.11
The top few tables show this (table, rows, reserved space, data, index, unused, etc):
Activity 1143639 131 MB 89 MB 41768 KB 1648 KB 46% 1%
EventAttendance 883261 90 MB 58 MB 32264 KB 328 KB 54% 0%
Person 113437 31 MB 15 MB 15752 KB 912 KB 103% 3%
HouseholdMember 113443 12 MB 6 MB 5224 KB 432 KB 82% 4%
PostalAddress 48870 8 MB 6 MB 2200 KB 280 KB 36% 3%
The rest of the tables are either the same in size or smaller. No more than 50 tables.
Update 1:
- All tables use unique identifiers. Usually an int incremented by 1 per row.
I've also re-indexed everything.
I ran the dbcc shrink command as well as updating the usage before and after. And over and over. An interesting thing I found is that when I restarted the server and confirmed no one was using it (and no maintenance procs are running, this is a very new application -- under a week old) and when I went to run the shrink, every now and then it would say something about data changed. Googling yielded too few useful answers with the obvious not applying (it was 1am and I disconnected everyone, so it seems impossible that was really the case). The data was migrated via C# code which basically looked at another server and brought things over. The quantity of deletes, at this point in time, are probably under 50k in rows. Even if those rows were the biggest rows, that wouldn't be more than 100M I would imagine.
When I go to shrink via the GUI it reports 0% available to shrink, indicating that I've already gotten it as small as it thinks it can go.
Update 2:
sp_spaceused 'Activity' yields this (which seems right on the money):
Activity 1143639 134488 KB 91072 KB 41768 KB 1648 KB
Fill factor was 90.
All primary keys are ints.
Here is the command I used to 'updateusage':
DBCC UPDATEUSAGE(0);
Update 3:
Per Edosoft's request:
Image 111975 2407773 19262184
It appears as though the image table believes it's the 19GB portion.
I don't understand what this means though.
Is it really 19GB or is it misrepresented?
Update 4:
Talking to a co-worker and I found out that it's because of the pages, as someone else here has also state the potential for that. The only index on the image table is a clustered PK. Is this something I can fix or do I just have to deal with it?
The regular script shows the Image table to be 6MB in size.
Update 5:
I think I'm just going to have to deal with it after further research. The images have been resized to be roughly 2-5KB each and on a normal file system doesn't consume much space but on SqlServer it seems to consume considerably more. The real answer, in the long run, will likely be separating that table in to another partition or something similar.
