I have a db MSSQL for CAUIM. This db is mssql standart 2014 and weight it 1.27 tb to support 2162 devices.
All days to the 00:00 hours we have a backup full to CA_UIM db, this backup taking 4 to 5 hours in finished, during the backup the disk L (transaction log) increase to 90% average, the space for this disk is 400gb.
Is normal this increase on the disk (Transaction logs)?
If on the future we have more devices to monitoring and the weight of the db is 2tb and the backup take 6 hours to finished we need increased to 600gb to the disk L?.
usually at midnight is when the data_engine starts the process of moving data around and deleting data.
UIM best practice is to have the database in simple mode and this way the transaction log does not get too large.
It sounds like you have the database in full recovery mode so this would be expected.
Please see the MS SQL best practice Guide and the data_engine guides.
CA UIM (Nimsoft) Database Best Practices for MS SQ - CA Knowledge
hope this helps
Yes this is expected as Gene says.
There is plenty going on in the database during the maintenance period and you will see a spike in transaction log.
If in the case of a recover from backup you don't intend to apply the transaction log then there isn't much point in collecting this, so swap to simple mode, as already stated.
If you need to recover to a point in time, then you will need to keep the space available for the log.
Hi Gene_Howard rowan.collis Britta_Hoffner
Thank your for your answer.
For a long time we changed to mode recovery to simple but the problem "increase transaction log" was still happening.
From friday we made this:
1- We heighten the disk of 200gb to 400gb
2- We changed the schedule of the data_engine of 00:40 to 8:00 hours
After this changed the transaction log not increase of 5%
what we want is to see that this is a definite solution and not have the need to increase the L disk in the future.
Based on your experience, do you see this change well?