SQL Server 2008 R2 Express 10GB Filesize limit

Question

I have reached the file size limit on my SQL Server 2008 R2 Express database which I believe is 10Gb. I know this because I see Event ID 1101 in the event log.

Could not allocate a new page for database 'ExchangeBackup' because of insufficient disk space in filegroup 'PRIMARY'

I have removed some historic data to work around the problem for now but it is only a temporary fix. One table (PP4_MailBackup) is much larger than the others so when I created this database 12 months ago, I converted this table to be a Filestream table and the data is stored outside the FileGroup in the File System. This appeared to be working successfully until I received the error and new data was no longer being added to my database.

When I do a report on table sizes I see the Reserved(KB) column adds up to almost 10GB.

The folder that holds my FileStream data is 176 GB

The database .mdf file is indeed 10GB.

Does anyone have any idea why the table PP4_MailBackup is still using nearly 7GB?

Here is the "Standard Reports -> Disk Usage report" for this database:

Thanks in advance

David

Update

Here is some more info.

There are 868,520 rows in this table.

This cmd returns 1 so I'm assuming Ansipadding is on. I have never changed this from the default.

SELECT SESSIONPROPERTY('ANSI_PADDING')

The columns are defined like this

Even if every record for every column filled the full record size, by my rough calculation the table would be around 4,125,470,000 bytes. I understand that the nvarchar columns only use the actual space required.

I'm still missing a lot of space.

Answer 1

Not really an answer but more of a conclusion.

I have given up on this problem and resided myself to remove data to stay under the 10GB Primary file size limit. I figured out that the nvarchar columns store 2 bytes per character in order to deal with Unicode characters although they do only use the space required and don't pad out the column with spaces. So this would account for some of the space I can't find.

I tried to convert my char(500) columns to varchar(500) by adding new columns with the correct type copying data into them and then removing the old column. This worked but the table actually got bigger because removing the column is only a Meta data change and does not actually remove the data. To recover the space I would need to create a new table and copy the data across then remove the old table of course I don't have enough space in the primary file to do that.

I thought about copying the table to temp db removing the original table then copying it back but temp db doesn't support filestream columns (at least to my knowledge) so I would need to hold all 170GB within the temp db table. This sounded like a dubious solution and my test server didn't have enough space on the partition where temp db was stored. I couldn't find anything on the files size limit of tempdb on sql 2008 Express, but at this point it was all getting too hard.