March 5, 2010 at 9:40 am
In my environment, I've noticed some very large transaction log backup files. This is not a very large database and it is not being written to a lot. We do, however, have a large amount of stored procedures which utilize temporary storage (table variables, temp tables, etc). I realize that the creation of these objects will impact TempDB's transaction log size. However, I'm curious if it will impact my user database's transaction log size as well.
This system also currently implements a legacy hierarchal data structure that is queried by using a user-defined function which recurses through a CTE. Recursing through the CTE seems to be causing small amounts of writes.
Also, I'd like to know if there's any common way to reconcile which queries are causing transaction log growth. Would looking at a Profiler trace and focusing on the Writes column be a good start?
March 5, 2010 at 10:14 am
Robert Biddle (3/5/2010)
In my environment, I've noticed some very large transaction log backup files. This is not a very large database and it is not being written to a lot. We do, however, have a large amount of stored procedures which utilize temporary storage (table variables, temp tables, etc). I realize that the creation of these objects will impact TempDB's transaction log size. However, I'm curious if it will impact my user database's transaction log size as well.
No. Temp tables and table variables are stored in tempDB and changes to them will only affect tempDB's log. Only changes to tables within a database will affect that DB's log
This system also currently implements a legacy hierarchal data structure that is queried by using a user-defined function which recurses through a CTE. Recursing through the CTE seems to be causing small amounts of writes.
Probably work tables that the query processor needs to work through the hierarchy. Again, in tempDB.
What's your index rebuild frequency? That will cause a lot of log activity and it's something that usually catches people by surprise.
Lots of deletes?
Batch loads of data?
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
March 5, 2010 at 11:21 am
Index rebuilds are nightly.
There should be very few deletes, possibly more updates than necessary though.
March 5, 2010 at 11:46 am
Robert Biddle (3/5/2010)
Index rebuilds are nightly.
That'll cause the overnight log backups to be large
Other than that, if there's a time of day where they're consistently large, consider running a server-side trace over the period covered by the log backup, see what's running.
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
March 5, 2010 at 8:08 pm
I agree with what Gail said about indexes especially if the indexes have low cardinality where a lot of page and/or extent splits occur. Just that can make the log grow like crazy and then reorganization of such split indexes is also a logged operation. Of course, "over indexing" will exacerbate the problem, as well. You could use the "Sort in TempDB" option when rebuilding indexes to alleviate a bit of the problem but you need to make sure that you're not "over indexed".
Also, and I have to ask, you're not shrinking the DB each night are you?
I've also seen GUI code (and some DB code) that causes such a problem... it seems that some developers like to create a row and then update each "field" in the row separately. I've also seen it where some developers will delete an existing row and insert a new row instead of going through the rigors of simply updating a row.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply