Here in the command and control centre we monitor our servers whilst Persian cats sit in our laps. Unfortunately our tlog backup process has goes haywire. We have Powershell processes that backs up the logs. Every couple of months the PoSH process balloons to take all available memory. The .ldf files on the server total up to 100GB, the throughput on the servers remains relatively constant. The tlog back ups total a couple of MB. **My question is this. How exactly does SQL Server backup a tlog?** **Does the whole log get written into memory and processed (methinks not). OR does the log get scanned and and processed in small batches? What determines the amount of memory needed?** The cats are restless so a good answer would be very welcome.
I'm more inclined to think it's the PoSh script too. I've never seen memory issues from transaction log backups. And no, backups, neither log nor database, get loaded into memory, flushing the cache. Backup processes copy the extents from the disk and write them to the disk. It's pretty unlikely that the log backup process is the culprit here. More likely it's something in PowerShell, or @ThomasRushton question on third party products.
the PoSh script would be company confidential no doubt. It uses $smoBackup.SqlBackup($server). The script generates a log file which correlates with msdb.dbo.backupset. I know the log backup completes in a couple of seconds and that the server is under memory pressure. All I need to do now is capture info about my ballooning PoSh script. Perhaps I can ask the developers to to capture info about the current process and write that in the log as well.
As others mention is likely the script rather than the tlog backup. Set up some monitoring to see the memory usage by different processes. From here you'll be able to see what's what. You could also try to replicate the issue in test. Once you can you'll have the opportunity to try different things eg setting up a job to do the backups at the same frequency and see what occurs. From there you can try different scripts until you have narrowed down the fault but monitoring memory usage by process will be a good start. Hope that helps