|
|
changing recovery mode from FULL to BULK_LOGGED |
|
Author |
Message |
Kitep
|
Posted: 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
|
Posted: 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
|
Posted: 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
> >
> >
>
>
|
|
|
|
|
|
|