What happens when you hit the SQL Server Express 4GB / 10GB limit?
What kind of error occurs? What do users experience? Can you access the database using tools and what if you get it back under the 4GB / 10GB limit?
As I understand it you will start to see exception messages appear within your event log, such as:
Could not allocate space for object 'dbo.[table]' in database '[database]' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup.
If you can then reduce the size of the database, you can then continue to add etc as before. Tools should carry on working regardless of the database size.
Hope this helps!
It is too late but I tested it. The maximum size for SQL express 2008 R2 is 10240 MB for each database. After I reached that I got this error on insert:
Could not allocate space for object 'TableName'.'PK_Nme' in database 'DBName' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
After this I restart the SQL service, It stopped and started with no problem. I still could run Select, update delete command. (I did test update command and it was success, but I think If you update to something bigger it may throw error!)
You might see an error like this:
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.
Could not allocate space for object 'dbo.buyspace'.'PK__buyspace__4B5BD7F83A81B327' in database 'WAYTOOBIG' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
That's from our error log.
Users experience the application not working, usually beginning with strange errors that have bubbled up way too high.
Any app built on a Express should have as part of its initial planning - how do we keep the size down. The plan - we'll worry about it whenever is ok, until someone has to fork over the license fee. And that is usually after tons of frustration, down time, debugging, someone thought the problem was a full drive, someone else thought it was that new code release, etc, frustration, etc. and the user has spent some time looking for another vendor who can plan ahead. Which I'm sure is exactly what you are doing. Kudos to you for thinking of your user first!!!!!!!!!
Actions that increase the size will fail, e.g Insert, Update. You will still be able to access the database and run maintenance on it to bring the size back down.
I would setup some maintenance jobs to alert you before the database reaches this size so you can prevent any data being lost.
Probably you won't be able to insert any more data, but you will be able to select.
If I remember correctly, when it happened to us, the database just stopped. We had to do a backup to clear enough space to get in and resolve things.
You might like to know that SQL Server 2008 Express R2 now has a 10GB limit, so it might be worth upgrading.