April 1, 2009 at 4:02 pm
Hi,
We have a task which occurs frequently in the database (Server 2005) which does a huge amount of changes to a number of temporary tables and this in turn increases the logfile activity from about 1-2Gb an hour to over 15Gb an hour.
(I do understand that backups can occur more frequently, but that is not the question I am asking).
My Question is:
Is it possible to set a table so no logging occurs for that selected table so ?
Thnx
Wally
April 1, 2009 at 4:06 pm
wallynut (4/1/2009)
My Question is:Is it possible to set a table so no logging occurs for that selected table so ?
No.
Logging is never optional. It's essential for database consistency and to allow transaction rolllbacks. Even in simple, all changes are logged. The recovery mode just affects how long the data remains in the log.
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
April 1, 2009 at 4:17 pm
wallynut (4/1/2009)
Hi,We have a task which occurs frequently in the database (Server 2005) which does a huge amount of changes to a number of temporary tables and this in turn increases the logfile activity from about 1-2Gb an hour to over 15Gb an hour.
What changes exactly it is? Think of committing frequently. Think of changing the recovery model for minimal log
My Question is:
Is it possible to set a table so no logging occurs for that selected table so ?
As Gail already suggested nope not possible. it is there for consistency
April 1, 2009 at 4:23 pm
You can commit more frequently and possibly run more log backups.
You could put these tables into a separate database for the changes and then copy the data back when it's done changing, however then you still have logging (you could be in simple mode, and commit frequently).
Can you describe more of what you mean by changing?
April 1, 2009 at 6:13 pm
Hmm, according to this MSDN Blog, table variables are not logged. Of course you have other limitations there also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2009 at 7:34 pm
Yeah, Barry's right, table variables aren't logged, but what is it that you're doing that's causing so much logging?
"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 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply