February 16, 2010 at 9:20 am
Here is my issue, I have a database that is mostly tables that are truncated and repopulated through a series of DTS jobs that just transform the data from an HP3000, but there are some tables that dont get truncated and repopulated.
In order to protect this data from potential loss I have switched the database recovery model to 'full'.
I have been told this is unecessary and that logging can be turned off at the table level.
I know this isnt right and that everything is logged and how much is logged is controlled by the recovery model (which is at the database level). I have been searching BOL and Technet to find something that states specifically logging is always on and controlled at the database level by the recovery model, but have been unsuccessfull I am hoping someone can point me to a resource that will show this to be the case.
February 16, 2010 at 10:02 am
Paul Randal has a nice article about logging here: http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx?pr=blog.
Logging can't be "turned off" and it's never been controlled at a table level in SQL Server. Maybe you should ask whomever told you that logging is controllable at a table level to show you something to support their statement.
Greg
February 16, 2010 at 10:31 am
Yeah I have seen that article. I was hoping there would be something that states that transaction logging is controlled at the database level by selecting the proper recovery model.
Or at the the very least something that says transaction logging can not be set at the table level.
I know these to be true, but I need to show documentation to prove it.
Asking for proof of setting it at the table level is not an option as this is my boss's boss.
Thank you very much for your quick reply.
February 16, 2010 at 10:38 am
You're not going to find that negative documentation. I would insist that is not true, and if your boss' boss is sure, make a call to Microsoft support. They'll confirm it for $250.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 16, 2010 at 10:52 am
I didnt think I was going to find anything like that, seeing as how I couldnt on Technet or BOL. Just thought I would throw it out there and see what came out. Thank you all for taking the time to address this for me.
February 16, 2010 at 1:35 pm
michael.morse (2/16/2010)
Yeah I have seen that article. I was hoping there would be something that states that transaction logging is controlled at the database level by selecting the proper recovery model.
As Steve pointed out, Documentation is almost always written in a positive way ("A does B, C does D, etc.") and only rarely in a negative way ("X does not do Y") and almost never in a universal negative ("you cannot do Z in this product"). So, the onus to prove something generally lies with anyone making a positive claim. That is, the person who says that "A does B", is the person who should have to prove it, because "A does B", if true, should be documented.
An alternative route here is logic and reasoning, so you might try this out: "How can you recover any particular transaction in a database, if you are not logging every table in a database? Recovering only some of the tables in a transaction would automatically invalidate the integrity of a relational database."
[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]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply