4

I have what I believe is SQL Server 2008 R2 Express installed on a production server. When I connect to it with SQL Server Management Studio it shows up as SQL Server 10.50.1600.

If it is in fact SQL Server 2008 R2 Express, my understanding is that the database size limit is 10 GB. However, something is just bugging me because I am close to 1 GB in size and there is a message on the properties screen that is scaring me. The database is running in a production environment. There were a lot of rows in it and I deleted a lot, but it has not changed the message. When I go to the properties for the database it says "Size 1005.75 MB" and right below it says "Space Available 0.03 MB".

I tried to use "Shrink" but it just flashes and goes away. Is my production server about to crash because the database is too big? Why would that happen at 1 GB when I thought the server should allow 10 GB? How can I reduce it? Deleting records for inactive clients has done nothing.

Improve this question
2
  • Can you tell us what the actual message is?
    – Aaron Bertrand
    Aug 7, 2012 at 21:08
  • Sorry, I was unclear. There was no actual error message. I was concerned about limitations of the SQLExpress version, and when I saw the properties for the database said "Space Available 0.03 MB" I did not realize that was just the unallocated space and had an "Uh-oh" moment. Thanks for your response.
    – Norm Woodward
    Aug 7, 2012 at 21:51

3 Answers 3

Reset to default
6

The "Database Size" on the properties page is showing the size of the data file. This will be the size of your data, with some empty space for new data. You can configure the rate at which this empty space is allocated in the database properties.

It says space available 0.03Mb because you've shrunk the data file down - it will grow by itself to allow for new records - this is normal and intended behaviour. This "Available Space" is what's left in the current data file, NOT the difference between your current database size and the licensed limit of 10GB.

You can verify the edition that is installed by checking the General page in the Server properties under Product. In the management studio header it only shows the general version, not the product edition.

When you delete a lot of records, you will end up with free space left in the file. This will not be automatically shrunk unless you set up a maintenance task for it.

Improve this answer
3
  • Thanks very much for the quick and helpful answer. I just panicked because the size was very close to a nice round 1 GB number, and it was scary that it was saying there was no available space. I understand now. Could you steer me in a direction where I can set up maintenance to keep the database in good shape? I have done it with SQL Enterprise Manager, but SQLExpress has many limitations. Thanks again.
    – Norm Woodward
    Aug 7, 2012 at 21:36
  • I should also clarify. I have deleted a lot of old stale data in the past month, and yet the files and data size continue to get larger. I do backups of the database every night and the size of the backups continue to creep larger and larger, despite my reduction of data. I tried "Shrinking" the database but it did not change anything. I am searching for maintenance ideas on Google but so far no luck. Thanks again for your help.
    – Norm Woodward
    Aug 7, 2012 at 21:57
  • From a quick Google, Maintenance Plans aren't available in Express. If your database files are continually growing, check which recovery model you're using. If you're using Full Recovery and you're not backing up your transaction log, then this may be growing. Check msdn.microsoft.com/en-us/library/ms189275.aspx for details on the recovery models available.
    – Richard
    Aug 7, 2012 at 22:34
0

One thing you might want to do after you shrink it is to rebuild all of your indexes. Shrinking has a way of really fragmenting indexes. I use the following script to reindex everything if I have to shrink [you shouldn't shrink a datbase unless you are running out of space on the server!] DECLARE @TableName sysname DECLARE cur_reindex CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' OPEN cur_reindex FETCH NEXT FROM cur_reindex INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Reindexing ' + @TableName + ' table' DBCC DBREINDEX (@TableName, ' ', 80) FETCH NEXT FROM cur_reindex INTO @TableName END CLOSE cur_reindex DEALLOCATE cur_reindex

Improve this answer
0

Your available size is just the size left in your database file. It will autogrow if it is needed until your own specified limit or until your database reaches the sql server edition limit.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.