February 13, 2013 at 6:14 pm
Hi, I have a sproc that does millions of deletes every hour. It grew everytime i manually set the date range in the sp and executed the alter statement in the sp and ran it. But, then when i went I put in the date range in the variable and executed the alter statement on the sp again for the last time it grew again initially, but then it stopped growing after that even though it was executing every hour? But, the deletes are occuring cause i see the records going down? Can somebody explain why the trans log is not growing when it's supposed to be?
Appreciate it.
February 13, 2013 at 11:35 pm
Probably because you have log backups scheduled that are marking the log as reusable. That way the later deletes reuse the same space as the first set
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2013 at 10:27 am
Oh interesting that would explain it! This client does have their own trans log backup system and one of my concerns was that they had hourly trans log back up but i didn't see the trans log clearing out like when we do it in the sql world. So, like you said that system they use probably has the trans log backup marked as reusable. Good to know. Appreciate it so much!
February 14, 2013 at 2:16 pm
ok i think here is an article that helped clear things up. Read the "Transaction Log Architecture" section. Basically, the trans log is a endless circle and the trans log backup will mark the virtual logs that are being backed up to through the trans log backup and when the next round of deletes happens it uses the inactive virtual logs. So, that is why we are seeing the Trans log grow and then not grow cause we resuse the tran log. Awesome good to know. Thanks.
http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
February 14, 2013 at 2:22 pm
Yup, exactly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply