![]() None these times was funny at all! Backup your transaction log regularly Transaction logĪ couple of times in my personal experience, sudden high TLog growths have filled the disks and the alarms start to fire everywhere. Which high availability solution you use depends a lot on your budget and the system configuration, but anything is better that nothing at all. Mandatory for critical databases, even when the configuration of those can be a little more difficult due to the size of the databases, in case of a failure this can save your life, is always faster just to move to your secondary database copy than perform a full restore of the backups. Differential backup is your friendĪ good backup strategy must consider using differential backups for big databases, since these backups only store the changes from last full backup checkpoint, the size of the file will become larger as the time of the full backup increases, so you have to find a balance between full/diff backups that better suits your needs. ![]() Filegroup backupĪs stated above, consider separate your data into multiple datafiles and filegroups, this way using the backup filegroup option, you can use any business logic you want to achieve that, so more critical data can be backed up more frequently than the whole set, this also reduces your RTOs for your databases. Out there since 2008, this feature is not commonly used, because it is not active by default, this can save you a lot of disk space for backups and reduce the backup time. If you don't have an adequate configuration for your VLDBs, the backup and restore windows can be also an issue, especially if you have tight RTOs for critical systems. If your database has high resource consumption, maybe this is not for you. Page compression usually saves more disk space, but also have a higher resource consumption for compress and decompress data. Data compressionĪnother feature of SQL Server is to row and page compression to reduce the disk space, the only drawback is that you have to consume more system resources to do the decompression when data is accessed. Remove the percent autogrowth feature, 10% of 1 GB is not the same as 10% of 1 TB, always prefer fixed sizes. When creating new datafiles, set the expected size upon creation, don't rely on the auto-growth feature, as is recommended to have it enabled as a safeguard, always expand your datafiles manually with the expected size. You can set an alarm (email, SMS, IM) when certain threshold is reached, for example a disk having 80% space used, so you have enough response time to check what is going on. Put in place regular database growth monitoring (how you achieve that is up to you), but a proper alarm in time can avoid you a unpleasant outage or a critical ticket. As you database becomes bigger and bigger, you should consider splitting your database into multiple filegroups, you can move big historical or log tables into a different filegroup and even mark them as read only, or separate them by business rules so that way, more critical data can be backed up more often using filegroup backups (more on this later on). ![]() Most of the databases start with only one default filegroup. Next step is to order the datafiles following best practices, separate datafiles in different drives as follows (if possible): SQL Files locationīe aware of the location of each data and log file for your instance, find out how much space is taking each database file and if they are splitted across several files/folders. Maybe the most common task to deal with VLDs, a real headache if you have been handover the server with no further info and you have to optimize the disk space. I want to share you some tips to help to make your life easier with a variety of common tasks you can encounter. If you are like me, and have SQL Server instances with databases of Terabytes of data, also known as very large database or VLDB, and you have to perform any kind of task on a reasonable time window, while minimizing the outage and keep the user happy with overall performance of the instance. Stress everywhere, user wants everything "Para ayer"
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |