Our Application's Database is becoming very large for one of our Customers. This Customers's DBA mentioned this to me, worrying that the DB is getting too large. It's about 350GB currently.
So first I looked at the unused space in the Database (i.e. sysfiles.size - fileproperty 'SpaceUsed'). And there was 75GB unused in the Database. Which is about 20%.
I mentioned that there was 75GB/20% of unused space in the Data File. The DBA answered keeping 20% free in the Database was their standard. I was surprised at this answer. Does anyone else do this? I thought the idea is to make sure you have a certain percentage of your Drive free, not a percentage of free space inside the Data File.
I would have thought it should "depend" .. rather than "keeping 20% as standard"
Having slack space where rows expand, and indexes have keys added randomly (i.e. FILL FACTOR < 100% to reduce chance of index page splits) makes sense, but (for example) for an index which is only ever added to with increasing-value keys it would be a waste, surely?
So perhaps they should change their policy to a case-by-case basis ... although if they are only going to save 5% on a 350GB database maybe its not worth it.
Any indexes (big ones!) that are unused?
TLog file bigger than it should be? Not backed up as often as it could be? (assuming FULL Recovery model)
Archiveable data that could be moved to a separate database and stored more efficiently perhaps? Fewer indexes, or no slack space (on the grounds that it is essentially read only)
Index rebuilds copying the large table to "fresh space" and extending the file / leaving a large hole in it. (Not sure there is a solution that that! but we reorganise large tables, rather than rebuilding them)
Change indexes to be Filtered if there are a large number of irrelevant entries in them. For example, if a column which is NULLable has an index and is only ever queried for non-NULL values then a Filtered Index WHERE MyColumn IS NOT NULL could save some space.
Probably a bunch of other such space-saving tricks ... would be interested to hear of any that other folk use / consider worthwhile.
1 Like
Thanks - good point about the unused indexes. I think you're right, a lot of space with unused indexes.
We were just looking at the Data file, not the TLog.
... anyways, if they actually are trying to maintain 20% unused space inside the Data File - that means they need to continually monitor the unused space - and then they need to increase the size of the Database File manually, if the unused space falls below 20%... just sounds odd to me
I would think most DBAs just set the Autogrow parameters and monitor the free space on the Hard Drive
Ah, i see, that sort of unused space 
Yes I agree, much easier to use Auto Grow and monitor.
Having fixed database file size (if that is what they are doing) runs the risk that the file becomes full, when there is still spare space on the disk, and likely to cause mayhem as a result. If everything neatly has BEGIN TRANSACTION and COMMIT / ROLLBACK then a file-full will have no consequences, I expect, but I would think it far more likely that there was the odd place in the code where there were multiple INSERTS / UPDATES without a Transaction. Whilst we always intend to have transactions there are probably places in our code where they have been missed - it would be very hard to test every such instance.
Thanks. I doubt they have fixed size (i.e. auto-growth off). Maybe they are increasing manually but still have auto-growth on, just in case. Or maybe they just didn't want to admit that they didn't realize 20% of the database file was unused.
I think I'll just ask them what they are up to...
Many DBAs leave autogrow on for data files, but never want it to be used, i.e., they want to pre-allocate the same themselves instead. That approach was much more critical before IFI became available for data files in SQL Server.
The other reason for that is to make sure you don't have a large number of (relatively) small added file space, instead adding it all in one larger chunk. That reduces physical fragmentation of the file, which helps performance.
That said, an arbitrary "20%" doesn't make much sense, since for 750GB, as in this case, it results in a lot of extra space reserved, which may not be used for a year or more.
The amount of extra reserved space in a db file should be based on how fast that db file is growing, not on a generic number applied to every file.
2 Likes