/
3 SQL data maintenance

3 SQL data maintenance

Backing Up the Transaction Log

Data are stored in SQL servers in several files. The most common scenario is storing data in a file with the .mdf extension and storing the transaction log in a file with the .ldf extension.

 

The MDF file contains purely data. The LDF file contains the aforementioned transaction log, which can change its size during program operation to multiple times its original size (e.g., several tens of GB). Regularly backing up the transaction log (TRN) reduces its size, thus not taking up disk space and making data operations faster. How to back up this TRN will be shown below. There are several options, but one is directly in the properties of the specific database.

 

In SQL Studio, right-click on our database, select "Properties" from the menu, and on the right side of the dialog is the Transaction LOG option. Here, it is necessary to set the destination where the backup files will be stored. Both methods must be set, namely local storage and storage using a UNC path (both destinations can point to the same location). The validity of one backup is not set to one day because after a full backup, we will no longer need the TRN. From these TRN backups, it is possible to restore the entire database or just a previous state if needed, e.g., from 30 minutes ago. Below in the image is an example of settings where the backup will be stored every 30 minutes.

 

The backup interval for TRN is to be considered. If more than 10 users are working in the system, it is likely that there will be a greater number of data accesses during their work, thus increasing the TRN. The recommended value is every 30 minutes. If the TRN is backed up frequently, such as the mentioned 30 minutes, it will not slow down the server, and the backup files will not be too large. On the contrary, if the TRN backup is performed, for example, every 2 hours and there are usually 15 people logged into the system, one backup will take longer, the server will be more burdened, and the backup TRN files will be larger. With a 30-minute interval, the time to create such a backup is from 1 to 3 minutes.

 

The TRN backup cannot be started on a database that has not yet undergone a full backup, so it is necessary to first create at least one full backup.

Due to a possible SQL Agent service outage or if TRN backup is not set at all, it can happen that the transaction log file (.ldf) grows to enormous sizes, and there is a risk that the server disk space will run out, or it may have already run out. In such a case, it takes a very long time to shrink it in the standard way, so this issue can be resolved quickly and efficiently.

 

We will do this in two steps.

  1. Again, in the properties of the specific database, go to the "Options" item and set the Recovery model to "Simple". Save OK.

2. We will again enter the properties of the database and go to the "Files" item. Here we will set the initial size for the LDF file to 50 MB.



We will save again - OK. The SQL server will now start resizing the transaction log - the LDF file. This operation may take several minutes depending on the performance of the SQL server and available RAM. Once completed, the LDF file will be at a maximum size of 50MB. Once we have resolved the issue with the huge LDF file, it is necessary to switch back the Recovery Model setting to "Full" and immediately set up TRN backups as mentioned above and perform a full database backup; otherwise, the TRN backup cannot proceed.

Example of a Possible Basic Data Maintenance Plan for SQL Server

Below is a proposal for a possible data maintenance plan for SQL Server. The plan consists of five basic tasks.

  1. Backup TRN - described above

  2. Backup DB - Full, daily backup of SQL data. Frequency: once a day

  3. Shrink DB - Database optimization. Frequency: once a day

  4. Reorganize index - Defragments and sorts indexes in databases, ensuring optimal size and fragmentation of indexes. It is advisable to schedule this once a week on non-working days or during nighttime hours

  5. Clean - Removal and cleaning of the scheduler history. Frequency: once a week, and it is possible to choose how old records will be removed from history.