Unaccounted for database size

Question

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.

Answer 1

Did you try the dbcc command to shrink the catalog? If you transfer all data to an empty catalog, is it also 20GB?

A database uses a page-based file system, so you might be running into a lot of slack (empty space between pages) due to heavy row removal: if the dbms expects rows to be inserted at that spot, it might be better to leave the spots open. Do you use unique_identifier based PK's which have a clustered index?

Answer 2

You may also want to update the usage in the systables before you run the query to make sure that they are accurate.

DECLARE @DbName NVARCHAR(128)
SET @DbName = DB_NAME(DB_ID())
DBCC UPDATEUSAGE(@DbName)

Answer 3

you could try doing a database vacuum, this can often yield large space improvements if you have never done it before.

hope this helps.

Answer 4

Have you checked the stats under the "Shrink Database" dialog? In SQL Server Management Studio (2005 / 2008), right-click the database, click Tasks -> Shrink -> Database. That'll show you how much space is allocated to the DB, and how much of that allocated space is currently unused.

Answer 5

what is the fill factor you're using in your reindexing? it has to be high. from 90-100% depending on the PK datatype. if your fill factor is low then you'll have a lot of half empty pages which can't be shrunk down.

Answer 6

Try this query:

SELECT object_name(object_id)  AS name, rows,  total_pages, 
  total_pages * 8192 / 1024 as [Size(Kb)]
FROM sys.partitions p
INNER JOIN sys.allocation_units a
  ON p.partition_id = a.container_id

Answer 7

Have you ensured that the space isn't being consumed by your transaction log? If you're in full recovery mode, the t-log won't be shrinkable until you perform a transaction log backup.