May 6, 2005 at 9:34 am
OK, this may be a pretty simple question, but.... Here goes.
Here's the setup: We have an Oracle data warehouse that our data provider..... provides. They keep month-end data for the previous 2 months. We need to hold on to more than that. I have the data warehouse set up as a linked server within one of our SQL servers. Every month, a stored procedure runs which does an INSERT..SELECT, grabbing data we need from the data warehouse and putting it into a table in a local database.
The problem is that when this happens, the transaction log gets HUGE. It keeps growing and growing. After the next backup, we're left with a ton of unused space in that database.
I'm wondering - is there a way for me to specify that I don't need this INSERT to be logged? Should I just set some options like "Truncate Log on Checkpoint", and set the recovery model to Simple - would that do what I want it to? I was under the impression that even setting these would still have the log grow to enormous, it would just clear itself out after the INSERT is done...
The T-SQL group may not have been the right one for this question - I was just thinking if there's a SQL command I can do to say "don't log this INSERT", I'd be set. I don't think I can do a bulk insert, because that seems to want to come from a file - right? Anyone have any advice? Sorry if this has been answered before - I did do a search before I posted and came up empty.... Thanks in advance! sqlservercentral.com rules! whooo! (sorry, possibly still bombed from last night. cinco de mayo, you know)
May 6, 2005 at 10:45 am
As an alternative, consisder using the Data Transformation Services's Transform Data Task with the fast load option combined with setting the database to use the BULK_LOGGED recovery option. From BOL:
"When BULK_LOGGED is specified, logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space. In exchange for better performance and less log space usage, the risk of exposure to loss is greater than with full recovery"
ALTER DATABASE ? RECOVERY BULK_LOGGED
SQL = Scarcely Qualifies as a Language
May 6, 2005 at 11:32 am
Ahh - cool. I will give that a try! Thanks for the response.
May 9, 2005 at 1:39 am
In addition to Carl's comment, see if this helps:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply