Considerations for when your small database reaches VLDB status
You started with a small database with only a few users, business is going great and before you know it your small database of a few GB’s grows into the TB scale with loads of users!
This is great from a business point of view but VLDB’s complicate maintenance and backup/restore operations, something you didn’t have to worry about when your database was still a few GB’s.
Here are some things to consider when you’re to deal with VLDB’s:
- Growth of data and log files
If your database is growing at a very fast rate make sure grow your data and log files before they’re full. If you can disable auto growth and manually grow both files when there is still free space left in the reservation.
Auto growth can impact performance when it occurs and if you use a small auto growth size it will occur frequently which can be a performance killer.
Keep an eye on your database growth so you can react before it’s too late!
- Index maintenance
Maintenance on indexes can take a lot longer to complete. You might even need to consider to spread your index maintenance over several days depending on how quick they get fragmented again or your maintenance window.
- Temp DB growth and pressure
As your database grows so does your temp DB. Consistency checks (you are doing consistency checks…right…?) can put heavy pressure on your temp DB as do certain query operations.
Make sure you monitor the usage and size if your temp DB and scale it accordingly. When experiencing temp DB contention it might be a good idea to split your Temp DB data files. Paul Randal has an excellent article about how many files here.
- Consistency Checks
As your database grows consistency checks are going to take a whole lot longer to complete. Should you drop them then? Offcourse not!
The first option you should consider is running DBCC CHECKDB() with the PHYSICAL ONLY parameter. This performs more or less the same checks but way faster.
If that’s still not fast enough consider spreading your CHECKDB() over multiple days where you check X amount of tables a day with the CHECKTABLE parameter.
- Log shipping
I know a lot of people who use log shipping as HA option. Log shipping is a fine option until your database maintenance starts to generate huge transaction log backups. As those backups get larger it’s going to take a lot longer to restore them on target server which basically means that your log shipping environment will be out of sync. Having to do a switch to your log ship subscriber when it’s out-of-sync for 3 hours or more isn’t a good way to start your disaster recovery scenario!
- Backup and restore
When your database reaches VLDB status your backups are going to increase in size as well which also means your restores are going to take longer. Keep this in mind when your planning for database recovery!
When backups are starting to take too long and no longer fit in the backup window you can consider partitioning your database in filegroups and perform a backup of each filegroup individually. Of course this can impact your recovery options so think carefully before you go down this road.
Another good option is running a backup operation to multiple files instead of just 1 backup file. While this might complicate your backup and restore a little it can be well worth the effort. Running a backup to multiple files will in most cases be a lot faster since you’re not limited to just one core during the backup operation but N cores depending on the number of files you’re writing your backup to.
Hopefully these considerations will help you manage your VLDB a little better!