Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

Thanks for any help.

share|improve this question
Can you tell us what the actual message is? – Aaron Bertrand Aug 7 '12 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 '12 at 21:51

migrated from stackoverflow.com Aug 8 '12 at 10:31

2 Answers

up vote 5 down vote

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.

share|improve this answer
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 '12 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 '12 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 '12 at 22:34
Thanks for your help. I appreciate it. – Norm Woodward Aug 8 '12 at 1:24
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.