changing recovery mode from FULL to BULK_LOGGED  
Author Message
Kitep





PostPosted: Wed Apr 21 11:42:09 CDT 2004 Top

SQL Server Developer >> changing recovery mode from FULL to BULK_LOGGED

Folks:

I'm looking at the different recover modes. I have setup a loop which dumps
several thousand records into my test db. I am not noticing any log-space
benefits by switching to the 'BULK_LOGGED' mode. I have setup my T-log at
2 Meg and it does not grow. I have a 10 meg database File which can grow.
I have one table in the DB - 'mytable' with a column called 'myDesc' which
is varchar(10). I do a database dump after switching recovery modes. Any
ideas what I'm missing here?

Thanks

Here is my loop:
-----------------
BEGIN
DECLARE


BEGIN
SET NOCOUNT ON



BEGIN



BEGIN

END
END
END
END
GO

Here are the stats:
-------------------
start T-Log MODE Rec's loaded before log is filled
======= ======= ======================
33% FULL 3,228
38% BL 2,914

38% BL 2,923
29% FULL 3,389



--
kent eilers


SQL Server201  
 
 
The





PostPosted: Wed Apr 21 11:42:09 CDT 2004 Top

SQL Server Developer >> changing recovery mode from FULL to BULK_LOGGED Kent,

You would see a difference if you used BCP utility, Bulk Insert statement,
or Bulk Insert DTS task. There is no difference in logging of Insert
statement.

Ilya



>
> Folks:
>
> I'm looking at the different recover modes. I have setup a loop which
dumps
> several thousand records into my test db. I am not noticing any log-space
> benefits by switching to the 'BULK_LOGGED' mode. I have setup my T-log
at
> 2 Meg and it does not grow. I have a 10 meg database File which can grow.
> I have one table in the DB - 'mytable' with a column called 'myDesc' which
> is varchar(10). I do a database dump after switching recovery modes. Any
> ideas what I'm missing here?
>
> Thanks
>
> Here is my loop:
> -----------------
> BEGIN
> DECLARE


> BEGIN
> SET NOCOUNT ON



> BEGIN



> BEGIN

> END
> END
> END
> END
> GO
>
> Here are the stats:
> -------------------
> start T-Log MODE Rec's loaded before log is filled
> ======= ======= ======================
> 33% FULL 3,228
> 38% BL 2,914
>
> 38% BL 2,923
> 29% FULL 3,389
>
>
>
> --
> kent eilers

>
>


 
 
Kent





PostPosted: Wed Apr 21 12:03:07 CDT 2004 Top

SQL Server Developer >> changing recovery mode from FULL to BULK_LOGGED I'll give that a try

thanks
)



> Kent,
>
> You would see a difference if you used BCP utility, Bulk Insert statement,
> or Bulk Insert DTS task. There is no difference in logging of Insert
> statement.
>
> Ilya
>


> >
> > Folks:
> >
> > I'm looking at the different recover modes. I have setup a loop which
> dumps
> > several thousand records into my test db. I am not noticing any
log-space
> > benefits by switching to the 'BULK_LOGGED' mode. I have setup my T-log
> at
> > 2 Meg and it does not grow. I have a 10 meg database File which can
grow.
> > I have one table in the DB - 'mytable' with a column called 'myDesc'
which
> > is varchar(10). I do a database dump after switching recovery modes.
Any
> > ideas what I'm missing here?
> >
> > Thanks
> >
> > Here is my loop:
> > -----------------
> > BEGIN
> > DECLARE


> > BEGIN
> > SET NOCOUNT ON



> > BEGIN



> > BEGIN

> > END
> > END
> > END
> > END
> > GO
> >
> > Here are the stats:
> > -------------------
> > start T-Log MODE Rec's loaded before log is filled
> > ======= ======= ======================
> > 33% FULL 3,228
> > 38% BL 2,914
> >
> > 38% BL 2,923
> > 29% FULL 3,389
> >
> >
> >
> > --
> > kent eilers

> >
> >
>
>