Take the 2-minute tour ×
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.

We have a fairly big MS SQL 2008R2 database that resides on a SSD drive. The drive itself only has ~110Gb of space, and the database files are the only files on the drive.

The database is in "Simple" recovery mode, and only has two files, .MDF and .LDF.

The disk is now nearly full: the MDF is currently 109Gb in size. However, SSMS tells me there's nearly 18Gb of "Space Available" (in the 'General' properties page), and if I go through the motions of Shrinking the file it also tells me there's 18Gb of space free. SSMS also tells me the database size is around 132Gb, which surprises me - that wouldn't fit on the drive!

From what I've read, shrink is a really bad idea. However, I'm starting to see replication errors (could not allocate space for object). We've previously tried shrinking the database, but within a few hours the file was back to it's original size.

How should we proceed - given there's apparently 18Gb of free space, should SQL automatically use that free space? Or is it as simple as: we really need more disk space?

share|improve this question
1  
I'm not convinced the replication errors are related, but it would be good to understand why - after a shrink - the data file is back up to the larger size that quickly. Are you sure you're talking about the data file and not the log file? What kind of write activity do you have going to the database? Which table(s) are the ones that are occupying the most space? –  Aaron Bertrand May 12 '13 at 18:26

2 Answers 2

up vote 5 down vote accepted

Space is available inside the database because data has been moved around. Perhaps you have very high levels of page splits, or have recently deleted a large portion of data that had previously caused the data file to grow.

SQL Server does not shrink database files automatically when you've freed up space within them, because the logical assumption is that if you've used that space once, you'll use it again. Autogrow can be an expensive event and unnecessary if you've only freed up space temporarily (what were you able to do with all that free space in the meantime?). For the same reasons, you shouldn't try to temporarily reclaim space, either. Just let SQL Server use the 18 GB of available space as you add more data. If you think you will need more than 18 GB of additional space going forward (in which case, you will need to add file(s) on other disks, or move to a bigger disk).

sp_spaceused (and in turn the UI dialog you're looking at) may return more space than is possible because of synchronization issues in the metadata about your tables/indexes/files. In order to make sure it reflects accurate space, run this:

DBCC UPDATEUSAGE(0);

I have also seen scenarios where rebuilding indexes was required in order to rectify the counts/space, but I haven't seen that specific case since SQL Server 2000.

(I suspect this isn't a simple case where your database already spans multiple disks, or you surely would have mentioned that in the question.)

That all said, the fact that when you shrink the data file expands almost immediately leads me to believe that you are actually using the space, but must also be performing big deletes or updates that are freeing it up (which is when you see 18 GB free). Unfortunately it's impossible for us to see exactly why the data file is expanding and then clearing itself out - perhaps you have transactions where you are truncating / re-populating large tables, performing massive archive operations, etc.

share|improve this answer
    
DBCC UPDATEUSAGE did the trick - the UI now shows the database size of just under 110Gb, which is what I'd expect. And yes, there's been a lot of big updates/deletes recently - so as long as the 18Gb of "free space" will be used I'm hoping everything will work out. Thanks for your help! –  KenD May 12 '13 at 18:44

SQL Server allocates disk space for a file (.mdf or .ldf) when the space is needed (according to your settings of course), then the file remains the same size even if transaction log (.ldf) is cleared or data (.mdf) is deleted.
That explains why you see 18Gb of "Space Available", and that space is only available inside your file, not on the disk (it is allocated).
That is also the reason that shrinking didn't help you - DBCC SHRINK[FILE|DATABASE] is sometimes useful after exceptionally large operations - in your case the deallocated space was later reallocated which means it was needed.

Your database may be bigger than the drive if your transaction log file (.ldf) is located on a different disk or if you have more than one data file (and the other file is on a different disk) .

It is possible that you have old data that you don't need and can be deleted,
after such operation shrinking will probably be useful.

Another option is to archive old records into partitions or tables on a different FILE on a different disk.

Of course you can move your entire database to a different (larger) disk if the previous option was not satisfying.

share|improve this answer
1  
Some good points in there. I'd be even more conservative recommendation for shrinking files. Namely: don't do it unless it's an emergency stopgap maneuver –  swasheck May 12 '13 at 17:05
    
Thanks. As mentioned above, the database only has two files, the data file and the log file - so it's a bit of a mystery why the database size is reported as bigger than the physical disk! Also, I've found a script that reports on the size of the actual data, and there's only 80-odd Gb of data actually in there. I might end up copying the data out of the tables (there's only a few) to a separate database file on a different disk and recreating it. –  KenD May 12 '13 at 18:00

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.