Recovery Mode Full vs Bulk_Logged

I am trying to determine how to create smaller log backups during indexing.
I am using SQL2K SP3 and currently reindex half of my 60GB each week
producing 30 GB of log file dumps. Readings from various sources suggest
that changing the db's recovery to BULK_LOGGED from FULL while reindexing
results in reduced logging. After changing to BULK_LOGGED while indexing, my
dumps are still 30 GB. My initial testing confirmed that the log did not
grow as large so I am surprised that the dumpfiles are still just as large.
The backup files are about the same size regardless of recovery setting.
For a test I restored my database to a test server and checked to make sure
the recovery mode was still FULL. I then set up a maintenance plan to dump
the log every 10 minutes. I let this run through 2 dumps of the log. I then
did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
transaction log as shown in EM with the taskpad view. When the maintenance
job ran it produced a 1330 MB dumpfile as expected and reduced the amount of
space used in the trans log as shown in EM. I then changed the recovery mode
to BULK_LOGGED and allowed the maintenance job to run several times. I then
did dbcc reindex on a different large table. The transaction log grew only a
small amount but when the log dumped it created a 675 MB file! Not believing
this I detached the db and deleted the old tran log. I then reattached the
db with a new, clean log of 504 KB. I allowed the log to dump creating a 79
KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
large table. It grew the transaction log to 35 MB of which only 17.7 MB was
used (The log was set to grow by 10%). Why was only 17.7 MB used but the log
grew to 35 MB? I would think that it would have had to have at least 32 MB
used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the trans
log dumped to disk. The file was 648 MB. How does 35 MB of Trans log create
a dump file 648 MB in size?
Can anyone explain this?It is all dependant on how SQL stores, backs up, and restores log
information. The BULK LOGGED recovery model writes allocation information
to the log instead of full transactional information when BULK INSERT or one
of its variants is used. This would not be enough to recover, so when you
back up the log SQL adds in the data, expanding the log backup so you can
apply it and recover the database.
So, bulk logged will save on-disk space, but not backup space.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> I am trying to determine how to create smaller log backups during
indexing.
> I am using SQL2K SP3 and currently reindex half of my 60GB each week
> producing 30 GB of log file dumps. Readings from various sources suggest
> that changing the db's recovery to BULK_LOGGED from FULL while reindexing
> results in reduced logging. After changing to BULK_LOGGED while indexing,
my
> dumps are still 30 GB. My initial testing confirmed that the log did not
> grow as large so I am surprised that the dumpfiles are still just as
large.
> The backup files are about the same size regardless of recovery setting.
> For a test I restored my database to a test server and checked to make
sure
> the recovery mode was still FULL. I then set up a maintenance plan to dump
> the log every 10 minutes. I let this run through 2 dumps of the log. I
then
> did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
> transaction log as shown in EM with the taskpad view. When the maintenance
> job ran it produced a 1330 MB dumpfile as expected and reduced the amount
of
> space used in the trans log as shown in EM. I then changed the recovery
mode
> to BULK_LOGGED and allowed the maintenance job to run several times. I
then
> did dbcc reindex on a different large table. The transaction log grew only
a
> small amount but when the log dumped it created a 675 MB file! Not
believing
> this I detached the db and deleted the old tran log. I then reattached the
> db with a new, clean log of 504 KB. I allowed the log to dump creating a
79
> KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> large table. It grew the transaction log to 35 MB of which only 17.7 MB
was
> used (The log was set to grow by 10%). Why was only 17.7 MB used but the
log
> grew to 35 MB? I would think that it would have had to have at least 32 MB
> used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
trans
> log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
create
> a dump file 648 MB in size?
> Can anyone explain this?
>|||I tackled the same problem by using sqllitespeed backup.
richard
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> I am trying to determine how to create smaller log backups during
indexing.
> I am using SQL2K SP3 and currently reindex half of my 60GB each week
> producing 30 GB of log file dumps. Readings from various sources suggest
> that changing the db's recovery to BULK_LOGGED from FULL while reindexing
> results in reduced logging. After changing to BULK_LOGGED while indexing,
my
> dumps are still 30 GB. My initial testing confirmed that the log did not
> grow as large so I am surprised that the dumpfiles are still just as
large.
> The backup files are about the same size regardless of recovery setting.
> For a test I restored my database to a test server and checked to make
sure
> the recovery mode was still FULL. I then set up a maintenance plan to dump
> the log every 10 minutes. I let this run through 2 dumps of the log. I
then
> did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
> transaction log as shown in EM with the taskpad view. When the maintenance
> job ran it produced a 1330 MB dumpfile as expected and reduced the amount
of
> space used in the trans log as shown in EM. I then changed the recovery
mode
> to BULK_LOGGED and allowed the maintenance job to run several times. I
then
> did dbcc reindex on a different large table. The transaction log grew only
a
> small amount but when the log dumped it created a 675 MB file! Not
believing
> this I detached the db and deleted the old tran log. I then reattached the
> db with a new, clean log of 504 KB. I allowed the log to dump creating a
79
> KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> large table. It grew the transaction log to 35 MB of which only 17.7 MB
was
> used (The log was set to grow by 10%). Why was only 17.7 MB used but the
log
> grew to 35 MB? I would think that it would have had to have at least 32 MB
> used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
trans
> log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
create
> a dump file 648 MB in size?
> Can anyone explain this?
>|||Geoff explained the reason for the behavior. I just want to add that an option can be to do DBCC
INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent interval (if at all).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dean Nicholson" <deann@.dtn.com> wrote in message news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> I am trying to determine how to create smaller log backups during indexing.
> I am using SQL2K SP3 and currently reindex half of my 60GB each week
> producing 30 GB of log file dumps. Readings from various sources suggest
> that changing the db's recovery to BULK_LOGGED from FULL while reindexing
> results in reduced logging. After changing to BULK_LOGGED while indexing, my
> dumps are still 30 GB. My initial testing confirmed that the log did not
> grow as large so I am surprised that the dumpfiles are still just as large.
> The backup files are about the same size regardless of recovery setting.
> For a test I restored my database to a test server and checked to make sure
> the recovery mode was still FULL. I then set up a maintenance plan to dump
> the log every 10 minutes. I let this run through 2 dumps of the log. I then
> did a dbcc dbreindex on a large table. It produced 1330 MB of log in the
> transaction log as shown in EM with the taskpad view. When the maintenance
> job ran it produced a 1330 MB dumpfile as expected and reduced the amount of
> space used in the trans log as shown in EM. I then changed the recovery mode
> to BULK_LOGGED and allowed the maintenance job to run several times. I then
> did dbcc reindex on a different large table. The transaction log grew only a
> small amount but when the log dumped it created a 675 MB file! Not believing
> this I detached the db and deleted the old tran log. I then reattached the
> db with a new, clean log of 504 KB. I allowed the log to dump creating a 79
> KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> large table. It grew the transaction log to 35 MB of which only 17.7 MB was
> used (The log was set to grow by 10%). Why was only 17.7 MB used but the log
> grew to 35 MB? I would think that it would have had to have at least 32 MB
> used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the trans
> log dumped to disk. The file was 648 MB. How does 35 MB of Trans log create
> a dump file 648 MB in size?
> Can anyone explain this?
>|||I have looked at BOL on this and have done some testing. My bosses ask 'IS
DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I can
prove. Safe I say I just have to trust MS. I suppose you recommend it? That
would help. Also DBREINDEX is easier to use, simple cursor to supply table
names. Do you have a script that will supply the necessary variables for
INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
think I want to do _WA_% or hind% indexes. I haven't found the correct
values to exclude these indexes from queries with out doing NOT LIKE .
Also. In response to Geoff's answer. How and where does SQL server save the
necessary info to use to fill in the info when it backs up everything to
disk?
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> Geoff explained the reason for the behavior. I just want to add that an
option can be to do DBCC
> INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent
interval (if at all).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Dean Nicholson" <deann@.dtn.com> wrote in message
news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> > I am trying to determine how to create smaller log backups during
indexing.
> > I am using SQL2K SP3 and currently reindex half of my 60GB each week
> > producing 30 GB of log file dumps. Readings from various sources suggest
> > that changing the db's recovery to BULK_LOGGED from FULL while
reindexing
> > results in reduced logging. After changing to BULK_LOGGED while
indexing, my
> > dumps are still 30 GB. My initial testing confirmed that the log did not
> > grow as large so I am surprised that the dumpfiles are still just as
large.
> > The backup files are about the same size regardless of recovery setting.
> >
> > For a test I restored my database to a test server and checked to make
sure
> > the recovery mode was still FULL. I then set up a maintenance plan to
dump
> > the log every 10 minutes. I let this run through 2 dumps of the log. I
then
> > did a dbcc dbreindex on a large table. It produced 1330 MB of log in
the
> > transaction log as shown in EM with the taskpad view. When the
maintenance
> > job ran it produced a 1330 MB dumpfile as expected and reduced the
amount of
> > space used in the trans log as shown in EM. I then changed the recovery
mode
> > to BULK_LOGGED and allowed the maintenance job to run several times. I
then
> > did dbcc reindex on a different large table. The transaction log grew
only a
> > small amount but when the log dumped it created a 675 MB file! Not
believing
> > this I detached the db and deleted the old tran log. I then reattached
the
> > db with a new, clean log of 504 KB. I allowed the log to dump creating
a 79
> > KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> > large table. It grew the transaction log to 35 MB of which only 17.7 MB
was
> > used (The log was set to grow by 10%). Why was only 17.7 MB used but the
log
> > grew to 35 MB? I would think that it would have had to have at least 32
MB
> > used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
trans
> > log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
create
> > a dump file 648 MB in size?
> >
> > Can anyone explain this?
> >
> >
>|||There is a template stored procedure in BOL under 'DBCC SHOWCONTIG' that
will run DBCC INDEXDEFRAG with the correct parameters. You may want to
modify it for your own needs.
SQL gets the data from the data file. You may find that some deletes are
blocked until you do a log backup once you complete a bulk-logged operation.
That is to make sure the data doesn't get lost.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Dean Nicholson" <deann@.dtn.com> wrote in message
news:Ofb5woO5DHA.2560@.TK2MSFTNGP09.phx.gbl...
> I have looked at BOL on this and have done some testing. My bosses ask 'IS
> DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I
can
> prove. Safe I say I just have to trust MS. I suppose you recommend it?
That
> would help. Also DBREINDEX is easier to use, simple cursor to supply table
> names. Do you have a script that will supply the necessary variables for
> INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
> think I want to do _WA_% or hind% indexes. I haven't found the correct
> values to exclude these indexes from queries with out doing NOT LIKE .
> Also. In response to Geoff's answer. How and where does SQL server save
the
> necessary info to use to fill in the info when it backs up everything to
> disk?
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> > Geoff explained the reason for the behavior. I just want to add that an
> option can be to do DBCC
> > INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent
> interval (if at all).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Dean Nicholson" <deann@.dtn.com> wrote in message
> news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> > > I am trying to determine how to create smaller log backups during
> indexing.
> > > I am using SQL2K SP3 and currently reindex half of my 60GB each week
> > > producing 30 GB of log file dumps. Readings from various sources
suggest
> > > that changing the db's recovery to BULK_LOGGED from FULL while
> reindexing
> > > results in reduced logging. After changing to BULK_LOGGED while
> indexing, my
> > > dumps are still 30 GB. My initial testing confirmed that the log did
not
> > > grow as large so I am surprised that the dumpfiles are still just as
> large.
> > > The backup files are about the same size regardless of recovery
setting.
> > >
> > > For a test I restored my database to a test server and checked to make
> sure
> > > the recovery mode was still FULL. I then set up a maintenance plan to
> dump
> > > the log every 10 minutes. I let this run through 2 dumps of the log. I
> then
> > > did a dbcc dbreindex on a large table. It produced 1330 MB of log in
> the
> > > transaction log as shown in EM with the taskpad view. When the
> maintenance
> > > job ran it produced a 1330 MB dumpfile as expected and reduced the
> amount of
> > > space used in the trans log as shown in EM. I then changed the
recovery
> mode
> > > to BULK_LOGGED and allowed the maintenance job to run several times. I
> then
> > > did dbcc reindex on a different large table. The transaction log grew
> only a
> > > small amount but when the log dumped it created a 675 MB file! Not
> believing
> > > this I detached the db and deleted the old tran log. I then reattached
> the
> > > db with a new, clean log of 504 KB. I allowed the log to dump
creating
> a 79
> > > KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on
another
> > > large table. It grew the transaction log to 35 MB of which only 17.7
MB
> was
> > > used (The log was set to grow by 10%). Why was only 17.7 MB used but
the
> log
> > > grew to 35 MB? I would think that it would have had to have at least
32
> MB
> > > used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
> trans
> > > log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
> create
> > > a dump file 648 MB in size?
> > >
> > > Can anyone explain this?
> > >
> > >
> >
> >
>|||On our internal SQL Server, I switched from DBREINDEX to INDEXDEFRAG about 6-9 month ago. I've had
not problems whatsoever. However, our databases are small (up to 2GB data) and we didn't really
suffer from performance issues in the first place.
I haven't read anywhere of any problems "safe-wise" with INDEXDEFRAG.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dean Nicholson" <deann@.dtn.com> wrote in message news:Ofb5woO5DHA.2560@.TK2MSFTNGP09.phx.gbl...
> I have looked at BOL on this and have done some testing. My bosses ask 'IS
> DBCC INDEXDEFRAG safe and effective? How can you prove it?' Effective I can
> prove. Safe I say I just have to trust MS. I suppose you recommend it? That
> would help. Also DBREINDEX is easier to use, simple cursor to supply table
> names. Do you have a script that will supply the necessary variables for
> INDEXDEFRAG. I have a db with 1200+ tables that have > 1 row and I don't
> think I want to do _WA_% or hind% indexes. I haven't found the correct
> values to exclude these indexes from queries with out doing NOT LIKE .
> Also. In response to Geoff's answer. How and where does SQL server save the
> necessary info to use to fill in the info when it backs up everything to
> disk?
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u%23nTsZM5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> > Geoff explained the reason for the behavior. I just want to add that an
> option can be to do DBCC
> > INDEXDEFRAG instead of DBREINDEX. And then do REINDEX at a less frequent
> interval (if at all).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Dean Nicholson" <deann@.dtn.com> wrote in message
> news:OmfdpEF5DHA.2720@.TK2MSFTNGP09.phx.gbl...
> > > I am trying to determine how to create smaller log backups during
> indexing.
> > > I am using SQL2K SP3 and currently reindex half of my 60GB each week
> > > producing 30 GB of log file dumps. Readings from various sources suggest
> > > that changing the db's recovery to BULK_LOGGED from FULL while
> reindexing
> > > results in reduced logging. After changing to BULK_LOGGED while
> indexing, my
> > > dumps are still 30 GB. My initial testing confirmed that the log did not
> > > grow as large so I am surprised that the dumpfiles are still just as
> large.
> > > The backup files are about the same size regardless of recovery setting.
> > >
> > > For a test I restored my database to a test server and checked to make
> sure
> > > the recovery mode was still FULL. I then set up a maintenance plan to
> dump
> > > the log every 10 minutes. I let this run through 2 dumps of the log. I
> then
> > > did a dbcc dbreindex on a large table. It produced 1330 MB of log in
> the
> > > transaction log as shown in EM with the taskpad view. When the
> maintenance
> > > job ran it produced a 1330 MB dumpfile as expected and reduced the
> amount of
> > > space used in the trans log as shown in EM. I then changed the recovery
> mode
> > > to BULK_LOGGED and allowed the maintenance job to run several times. I
> then
> > > did dbcc reindex on a different large table. The transaction log grew
> only a
> > > small amount but when the log dumped it created a 675 MB file! Not
> believing
> > > this I detached the db and deleted the old tran log. I then reattached
> the
> > > db with a new, clean log of 504 KB. I allowed the log to dump creating
> a 79
> > > KB file. While still in BULK_LOGGED mode I did dbcc dbreindex on another
> > > large table. It grew the transaction log to 35 MB of which only 17.7 MB
> was
> > > used (The log was set to grow by 10%). Why was only 17.7 MB used but the
> log
> > > grew to 35 MB? I would think that it would have had to have at least 32
> MB
> > > used for it to grow to 35 MB (32 MB * 1.10). The kicker was when the
> trans
> > > log dumped to disk. The file was 648 MB. How does 35 MB of Trans log
> create
> > > a dump file 648 MB in size?
> > >
> > > Can anyone explain this?
> > >
> > >
> >
> >
>sql