Why doesn't my transaction log grow with delete's? Recovery mode is set to FULL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Why doesn't my transaction log grow with...

Why doesn't my transaction log grow with delete's? Recovery mode is set to FULL

Rate Topic

Display Mode

Topic Options

Author

Message

JP10

Posted Wednesday, February 13, 2013 6:14 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 22, Visits: 139

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.

Post #1419786

GilaMonster

Posted Wednesday, February 13, 2013 11:35 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1419823

JP10

Posted Thursday, February 14, 2013 10:27 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 22, Visits: 139

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!

Post #1420193

JP10

Posted Thursday, February 14, 2013 2:16 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 22, Visits: 139

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/

Post #1420300

GilaMonster

Posted Thursday, February 14, 2013 2:22 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

Yup, exactly.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1420301

« Prev Topic | Next Topic »

Permissions