Impacts During And After The Shrink - The Best Practice For Transaction Logs Shrink

What can aid to advise any impacts during and after the shrink? Do we need to stop all jobs and processes in UiPath or even stop database connection? Can any best practices be shared for transaction logs shrink?

Resolution:
There should not be any impact while doing shrink operation, although this should not be performed on a regular basis.

This must remain an exception only because the transaction log file grew too much. It should figure out why the log file grew so much. If it's running 24/7 and it seems important, the backup strategy should be planned accordingly.

  • If a single backup once a week for example is sufficient and if Point in time recovery is not required, then a switch to a Simple recovery model can be recommended.
  • If the log file grows much huge when using Full recovery model, that might be because enough Transaction Log backup (i.e. every 5 to 30 min on DB with lots of transactions or less on smaller ones) is not performed or some very large query is run that does move a lot of data without frequent Transaction log backup.

If the Full recovery model is used, perform the below:

  1. Take a Transaction log backup (current usage should be close to 0%).
  2. Shrink to the desired size (500MB or 1GB or more than the current usage).
  3. Repeat steps 1-2. if the file size does not go down enough. It may take several runs on large files.

If the Simple recovery model is used, perform the below:

  1. Shrink to the desired size (500MB or 1GB or more than the current usage).
  2. Repeat steps1-2. if it doesn’t go down enough. It may take several runs on large files.

In the end, a file with a size of around 5MB or 1GB should be manageable. Growth should be set to a similar value (500 or 1GB but not X%).