Lately, I have come across a few DB servers that have .NDF files approaching 1TB in size, and while this is a far cry from being a VLDB, it does start to become a problem when you are running out of disk space… and in some cases due to the growth rate, you are running out FAST!
Example Scenario: So it’s Friday afternoon, you have been to the pub at lunch to bond with your colleagues, and now you are working on those proactive tasks that will bring recognition, value and glory to the team. Then, all of a sudden you get an alert, and it looks like one of your servers is not going to last the weekend!!! (Disk space, remember?)
A couple questions come to mind: Shouldn’t that alert have notified us earlier? And where was the capacity planning that would have predicted this growth? These will need to be addressed… but first and foremost we need to relieve the issue with disk pressure!
As DBA’s, addressing a disk that is filling-up-fast becomes our issue when we cannot get approval to throw more disk resource at a server. If it is a high profile or valuable system, then you will usually get your volume expanded. But sometimes the business case will not justify the expenditure, or perhaps the work cannot be carried out in time…
If you can prove that an increase in storage is justifiable, then it’s your job to provide this business case to management and get the ball rolling. Failing that, we are going to have to take action!
This means considering options such as using compression (if you can wear the performance trade-off), purging unneeded data (which is backed up elsewhere right!?), finding alternate storage (another server/UNC, another Data Center, offline storage), or maybe you can reclaim some space by removing unneeded files from the volume. If you need to get slightly more invasive, then we can look into removing some unneeded indexes…
All these options can reclaim some much needed space, but not all of them are an option, every time.
In our example scenario, it turns out that the largest tables were great candidates for implementing Page-Level compression, and in about one hour, we had plenty of space for the weekend to keep us out of trouble and the business happy. The below script can be used to enable Page-Level compression on a table.
So there you have it, space is no longer an immediate issue. However we are going to need a long term solution… and this got me thinking about ARCHIVING….
A full discussion on archiving is beyond the scope of this post, but I knocked up a quick demo on how we can achieve an elegant solution using the SWITCH statement and some PARTITIONING.
Stay tuned for the next post where this will be demonstrated…