February 28, 2010 at 10:16 pm
Is the following statement is valid,
ALTER TABLE table1 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
if not how this can be done in SQL Server 2005.
I don't want logging for an insert into statement that affects 3 million records
INSERT INTO TABLE1(.....)
SELECT * FROM TABLE2;
HERE THE LOG FILE IS INCREASED AND THERE IS PERFORMANCE ISSUE.
PLEASE HELP IF ANYBODY KNOWS ANY ALTERNATIVE FOR THIS.
March 1, 2010 at 12:02 am
Ajith Kumar A.J (2/28/2010)
Is the following statement is valid,ALTER TABLE table1 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
No. Where did that syntax come from? Looks like maybe Oracle.
if not how this can be done in SQL Server 2005.
I don't want logging for an insert into statement that affects 3 million records
Logging cannot be disabled in SQL server. It's not a particularly good thing to ask for anyway. If logging was be disabled and that insert failed for whatever reason, the entire database would be suspect and have to be restored from backup. No logging = no possibility of rollback = inconsistent database if the operation fails.
To quote one of the SQL dev team "We are a database that is supposed to follow the ACID properties, and unfortunately, the 'C' in ACID doesn't stand for Corrupt"
You can do bulk inserts of bcp, both of those are minimally logged in bulk-logged or simple recovery (see BoL for exact circumstances where they are and aren't). You can do SELECT INTO which is also minimally logged in bulk-logged or simple recovery. Or you can insert in batches with a log backup/checkpoint in between (depending on what recovery model you're using)
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 2, 2010 at 7:56 am
if I remember right DB2 uses that type of syntax
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply