February 26, 2015 at 12:13 pm
I was wondering if there is a way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.
The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.
Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?
Thanks.
February 26, 2015 at 12:39 pm
mark.worthen (2/26/2015)
I was wondering if there is a way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.
Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?
Thanks.
Not possible, at least directly. Many has been the time I have wanted to have non-logged stuff in SQL Server!!
1) You could do the preprocessing outside of SQL Server, then load just what is required (or a partially-distilled portion of it).
2) You could do the heavy-lifting in tempdb first. If you do this, be sure to size tempdb appropriately for the server to avoid a lot of growths/fragmentation. Some tempdb activity is not fully logged, and it is SIMPLE recovery. See option 3 on that.
3) You could do the heavy-lifting in a scratch database that was in SIMPLE recovery mode. This would still incur logging, but not in main database and if you don't have "one transaction to rule them all" the tlog could flush regularly to avoid getting huge.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 26, 2015 at 12:55 pm
Thanks Kevin,
We are doing the majority of the processing in temp tables but in the end, there are a lot of deletes and then writes back to five different summary tables. If we didn't use the temp tables, it would probably be 3x the amount of transactions being logged.
I hoped there was a way to get around the logging but feared it was not so.
Thanks!
February 26, 2015 at 2:33 pm
Depending on the specifics, it might be less overhead to save what you need from the summary tables, truncate them, then do a bulk insert of everything back to them in one statement.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 26, 2015 at 3:51 pm
If the load isn't one big transaction, maybe you could take the log backups more frequently during the load times. Might buy you better log re-use.
February 26, 2015 at 8:57 pm
Hmm. If you are deleting a large fraction of a table it is often best to drop existing non-clustered indexes and then rebuild after DML.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 27, 2015 at 6:33 am
You could try doing some of the work in 2014 using In-memory. It still logs things, but not in the same way, at all, especially if you use eventual consistency.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply