Can t-log size handle large number of deletions?

Hi All,
Im just wondering if there is a way i could find out if the size of my
transaction log will be able to record the number of deletions i will be
performing? Basically i have to delete 325 million rows from a table and my
t-log is, say 20GB. is there a way i can calculate if the t-log is big
enough or will it need to expand?
thx in advance.Well I certainly would not advocate deleting them all in one batch. If you
delete them in smaller batches (say 10K or 100K at a time) it will not only
be faster but you would have the option to backup or truncate the log as you
go along. How many rows in the table do you want to keep. It may be
easier to BCP out the ones to keep, truncate the table and bcp them back in.
--
Andrew J. Kelly
SQL Server MVP
"M Sandico" <msandico@.muchomail.com> wrote in message
news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Im just wondering if there is a way i could find out if the size of my
> transaction log will be able to record the number of deletions i will be
> performing? Basically i have to delete 325 million rows from a table and
my
> t-log is, say 20GB. is there a way i can calculate if the t-log is big
> enough or will it need to expand?
> thx in advance.
>|||You are correct in that I dont delete them all in one batch. I delete in
batches of 1000 with a BEGIN TRAN and COMMIT TRAN as well. What I am
planning to do is just set the model to SIMPLE during the deletion and have
the COMMIT TRAN force the log to be flushed during the automatic checkpoint
(when log is 70% full).
Just thought there would be a way to guesstimate how big a t-log youd need
for certain operations..
thx.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e9rVaHJpDHA.360@.TK2MSFTNGP12.phx.gbl...
> Well I certainly would not advocate deleting them all in one batch. If
you
> delete them in smaller batches (say 10K or 100K at a time) it will not
only
> be faster but you would have the option to backup or truncate the log as
you
> go along. How many rows in the table do you want to keep. It may be
> easier to BCP out the ones to keep, truncate the table and bcp them back
in.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "M Sandico" <msandico@.muchomail.com> wrote in message
> news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > Im just wondering if there is a way i could find out if the size of my
> > transaction log will be able to record the number of deletions i will be
> > performing? Basically i have to delete 325 million rows from a table and
> my
> > t-log is, say 20GB. is there a way i can calculate if the t-log is big
> > enough or will it need to expand?
> >
> > thx in advance.
> >
> >
>|||I don't know of a formula off hand. You would have to account for at least
the amount of data that you are deleting and then some percentage for
overhead etc. What that percentage is I don't really know.
--
Andrew J. Kelly
SQL Server MVP
"M Sandico" <msandico@.muchomail.com> wrote in message
news:ugg192JpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> You are correct in that I dont delete them all in one batch. I delete in
> batches of 1000 with a BEGIN TRAN and COMMIT TRAN as well. What I am
> planning to do is just set the model to SIMPLE during the deletion and
have
> the COMMIT TRAN force the log to be flushed during the automatic
checkpoint
> (when log is 70% full).
> Just thought there would be a way to guesstimate how big a t-log youd need
> for certain operations..
> thx.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e9rVaHJpDHA.360@.TK2MSFTNGP12.phx.gbl...
> > Well I certainly would not advocate deleting them all in one batch. If
> you
> > delete them in smaller batches (say 10K or 100K at a time) it will not
> only
> > be faster but you would have the option to backup or truncate the log as
> you
> > go along. How many rows in the table do you want to keep. It may be
> > easier to BCP out the ones to keep, truncate the table and bcp them back
> in.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "M Sandico" <msandico@.muchomail.com> wrote in message
> > news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > >
> > > Im just wondering if there is a way i could find out if the size of my
> > > transaction log will be able to record the number of deletions i will
be
> > > performing? Basically i have to delete 325 million rows from a table
and
> > my
> > > t-log is, say 20GB. is there a way i can calculate if the t-log is big
> > > enough or will it need to expand?
> > >
> > > thx in advance.
> > >
> > >
> >
> >
>