Re: Bulk Insert and sp_dboption
From: Russell Fields (RussellFields_at_NoMailPlease.Com)
Date: 06/25/04
- Next message: Brian Shannon: "Re: Transaction Logs"
- Previous message: Steven Yampolsky: "Re: Computed columns in temp tables"
- In reply to: SKG: "Re: Bulk Insert and sp_dboption"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 25 Jun 2004 08:45:27 -0400
If you need the data in TargetDatabase, then it seems to me that loading it
to IntermediateDatabase then copying it from IntermediateDatabase to
TargetDatabase only takes more time and resources. (Opinions from anybody
else?) And, in any case, you would likely use one of the bulk_logged
statements (SELECT INTO) to get the data between databases and all the same
issues would arise.
Note that the difference in logging for bulk_logged over full is that the
data in the bulk copy statements (Select Into, bcp, Bulk Insert, Create
Index, Writetext and Updatetext) is not logged in the transaction log.
What is logged is the allocations of space, etc, necessary for the health of
of the database.
If the logs are worrisome to you, you can:
1. Backup Transaction Log
2. Change to Bulk_Logged
3. Bulk Insert
4. Change back to Full
5. Backup Transaction Log
If you don't want to risk the loss of the bulk logged data, then keep it in
full mode and manage the transaction logs.
Russell Fields
PS If Bulk Insert causes blocking problems on your database, then that might
be a reason to stage the data elsewhere and move it in smaller chunks into
your target database.
"SKG" <sunil_godiyal@yahoo.com> wrote in message
news:#wj$cYiWEHA96@tk2msftngp13.phx.gbl...
> Thanks for the links.
> Russell i have to do bulk insert while other users are accessing database.
> Would you recommend using a different database for bulk insert and finally
> transferring info into the target database.
> This way i will load data faster with select into/bulkcopy on, without
> having to backup the log of current database.
> Thanks!!!
>
> "Russell Fields" <RussellFields@NoMailPlease.Com> wrote in message
> news:%231KrzHiWEHA.1368@TK2MSFTNGP10.phx.gbl...
> > SKG,
> >
> > Bulk Insert does light logging of the bulk inserts, but continues normal
> > logging of other operations.
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
> > _bkprst_4ku1.asp
> >
> > The granularity of recovery is no longer Point-in-Time, but is limited
to
> > the boundaries of Transaction Log Backups. An article from SQL Server
> > magazine also discusses this.
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag2k/
> > html/dbRecovery.asp
> >
> > Russell Fields
> > "SKG" <sunil_godiyal@yahoo.com> wrote in message
> > news:#nm3aCiWEHA.3716@TK2MSFTNGP11.phx.gbl...
> > > Iam doing bulk insert while other users are accessing the database.
> > > Does sp_dboption turns off logging for current session or for the
> database
> > > all sessions.
> > >
> > > How can i continue logging for other users, while iam doing bulk
> insert.?
> > > TIA
> > >
> > >
> > >
> > >
> >
> >
>
>
- Next message: Brian Shannon: "Re: Transaction Logs"
- Previous message: Steven Yampolsky: "Re: Computed columns in temp tables"
- In reply to: SKG: "Re: Bulk Insert and sp_dboption"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
