July 23, 2014 at 4:45 am
How many of those data files are read-only files that could be moved to another disk to free up space for the read-write files?
How much of the data in the databases could be pruned (data warehouse time, perhaps)?
How many databases are on that disk that could stand to have additional files created on another disk or just moved completely to another disk?
These are things to consider after all the other suggestions and would, as previously suggested, require more hardware if you don't already have it. In emergency situations, I have created size-restricted files on the OS drive to give the DB more growth potential. But it was a temporary measure to keep the server from going down until corporate finished setting up the addition SAN space for the server in question. Plus the server was a departmental "we need to fiddle" server, not a critical production server, which made this temporary measure relatively safe.
July 23, 2014 at 7:24 pm
paul.knibbs (7/23/2014)
Jeff Moden (7/21/2014)
samking (7/21/2014)
If the disk contains only data files...than what action we have to take......But it doesn't, does it. Notice that's not a question because you previously said it doesn't.
He didn't say that--the chap who originally created this thread 2 years ago said that. 🙂
]]
You're right. Thank you for the correction.
--Jeff Moden
July 23, 2014 at 8:05 pm
samking (7/21/2014)
If the disk contains only data files...than what action we have to take......
My apologies. For some reason, I thought you were the original poster. My only excuse is that I posted very early in the morning when I probably should have been in bed.
The combination of advice above would do you well with a slight modification. You need (at the very minimum) enough free space to rebuild and existing index to remove the "air" in it that may be "trapped" due to fragmentation (there is a trick around this, though. We'll get to that in a minute). The "easiest" thing, of course, would be to get more disk space. I say "easiest" in quotes because, depending on the corporate culture at work, it could take a long time to buy new disks. With a 90% full disk, you should start the purchase process now.
One of the folks above suggested that having a 90% full disk and a 90% full database are two different things and that's exactly correct. I'd first check and see how much free space is available in the database. It may be that you're not as close to running out of disk space as you might think.
If the DB doesn't actually have any free space, check for fragmentation on the indexes. Fragmentation can be cause by page and extent splits and those splits can waste huge amounts of disk space. The cure there would be to defragment the indexes that need it. Reorging the indexes doesn't take much space but will always be fully logged. If your log file is large enough, try reorging the fragmented indexes first. This could free up a huge amount of space. The problem with Reorging an index is that it reorgs only the leaf level. It won't reorg the B-TREE levels. If that doesn't affect your performance (and it might not depending on what you're queries are like), that might be good enough. If it's not, then you'll need to rebuild the indexes.
Like I said before, rebuilding the indexes (specifically, any index over 128 extents, which is only 8MB) will require at least as much free space as the index being rebuilt (my experience is about 120% of the size) even with SORT_IN_TEMPDB is turned on. There are, however, a couple of tricks you can do to get around that.
One way is to disable the index before your rebuild it. Of course, that will mean that the index is effectively set to zero rows and the index will be unavailable for use. If you disable a clustered index (you probably shouldn't), the table will no be usable until the clustered index is rebuilt. For Non-Clustered indexes, it effectively frees all of the space the index was using and rebuilds it instead of building a new copy of the index and then dropping the old one.
Another trick is to build the CREATE INDEX statement for the index you want to defrag and run it with the DROP EXISTING option. IIRC, that will also make the index unavailable. If it's the clustered index, it will make the table unavailable, as well, but a pinch is a pinch and you might want to try it on one of your larger fragmented indexes.
Of course, as previously identified above, if your indexes aren't badly fragmented or you have a proper index maintenance job that runs regularly, none of the above will be a big help. In such a case, you're going to need to get some physical disk space and make it available to the database. That's a pretty large subject on it's own and you might want to check Books Online for how to do that using extra files and, maybe even extra file groups depending on the need.
--Jeff Moden
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply