SQL Express Size Limit – Filestream Excepted


I’ve written a couple of blog posts about the Filestream feature that is available with SQL Server Express: one post on my blog (Store Images in the Database or Filesystem?) and one post as a guest writer on the SQL Server Driver for PHP Team Blog (Using the Filestream Functionality of SQL Server 2008). And (although I haven’t written about it), I’m aware that the size limit for SQL Server 2008 R2 Express has been increased to 10GB (up from 4GB). What I didn’t realize was that data stored as Filestream data doesn’t count toward that 10GB limit. I probably should have realized this on my own…it makes perfect sense, since Filestream data isn’t actually in the database. But, I didn’t realize it until someone actually pointed it out to me. So, if you are looking for ways to get more out of SQL Server Express, this is one of them.

That’s it for today…just wanted to share this simple realization. Hope it proves useful to someone.

Thanks.

-Brian

Share this on Twitter

Comments (3)

  1. David says:

    Great tip Brian! Question: When I run EXEC sp_spaceused on the db it includes the size of the filestream data (which surprised me).  Since the 10GB limit does not apply to filestream data, does MS calculate the database size magically by "disregarding" the filestream data?

  2. David says:

    Update – I found that the problem was my varbinary(max) column did not have the FILESTREAM attribute which was lost when I removed allow nulls from the designer.  When this happens apparently the filestream data is actually stored in the database which is why I was getting the bloated db size. After shrinking, the size came waaaaay down.

  3. Mike says:

    Thanks for this information.  I'm developing a system where I need to use SQL Express for the simple reason it's free.