asked
How Does the transaction Log Backup use memory
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.
Using SQL Server native backup, or a third party product? Any compression or encryption options?
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
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.
Are you executing the script on the box in question or are you executing it remotely? Have you tried it with the logging turned off? Perhaps run perfmon and capture some info to take back to the dev team to show them that the script causes unsuspected performance implications. Good luck :)
Is there a reason that a PoSH script is being used and not the tools that natively come with SQL Server? This is not a Problem I have seen using a conventional backup methodology.....
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
18 People are following this question.
