Best Way to Empty Transaction Log

Hi All,
What is a best way to empty (shrink) a database transaction log. I am
working with huge database - 10GB to 100GB- and basically doesn't care
about transaction log much when data is being deleted by a task. I have a
deletion task consisted of many stored procedures which is running every
night. In some of the client's database, when those stored procedure
deleted data in different tables, the transaction log getting to big and SQL
appears to hang up. Sometimes a deletion task will take a whole weekend.
Note that the stored procedures do use TRUNCATE TRANSACTION and CHECK POINT
trying to reduce the size of the transaction log.
My question is, how can I tell SQL not to log the transactions when my
deletion procedures are running. If there is no way to to tell SQL not to
log the transactions, is the a better way to control the size of the
transaction log file, other than using the CHECK POINT?
Thanks.A CHECKPOINT followed by a DBCC SHRINKFILE, works for us. We shrink our log
before backup everynight, the log itself will hit 100GB(400GB+ DB) over
weekend maintenace. Shrinkfile usually goes quick on the log.
" David N" <dq.ninh@.netiq.com> wrote in message
news:en54Y$qWDHA.2328@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> What is a best way to empty (shrink) a database transaction log. I am
> working with huge database - 10GB to 100GB- and basically doesn't care
> about transaction log much when data is being deleted by a task. I have a
> deletion task consisted of many stored procedures which is running every
> night. In some of the client's database, when those stored procedure
> deleted data in different tables, the transaction log getting to big and
SQL
> appears to hang up. Sometimes a deletion task will take a whole weekend.
> Note that the stored procedures do use TRUNCATE TRANSACTION and CHECK
POINT
> trying to reduce the size of the transaction log.
> My question is, how can I tell SQL not to log the transactions when my
> deletion procedures are running. If there is no way to to tell SQL not to
> log the transactions, is the a better way to control the size of the
> transaction log file, other than using the CHECK POINT?
> Thanks.
>|||Did not read well, sry. All your DELETE FROMs are going to be logged, that
is a good thing. If you are clearing out the entire table, you can use the
TRUNCATE TABLE which is non-logged. If you cannot use TRUNCATE TABLE and
must use DELETE FROM, you can try an optimize it to run more efficiently.
The DELETE might run quicker if you can base the delete off the CLUSTERED
INDEX(use temp tables if needed to achieve) or if this table is index heavy
you might drop some indexes(non-clustered only) and recreate them when the
delete process is finished.
My personal way if it is large delete filling up your log(ie one big logged
transaction). I would suck all the CLUSTERED INDEX values into a temp table
and use a WHILE with TOP functionality in it to loop the delete in smaller
digestible chunks. This will allow the log backups to clear out the
transaction log(make sure these are running in good intervals, ours go every
5mins). We have even added functionality to some of our bigger maint procs
to check logsize during loops and run a log backup to keep it in check.
HTH.
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
news:OsdM$DrWDHA.2032@.TK2MSFTNGP11.phx.gbl...
> A CHECKPOINT followed by a DBCC SHRINKFILE, works for us. We shrink our
log
> before backup everynight, the log itself will hit 100GB(400GB+ DB) over
> weekend maintenace. Shrinkfile usually goes quick on the log.
>
> " David N" <dq.ninh@.netiq.com> wrote in message
> news:en54Y$qWDHA.2328@.TK2MSFTNGP12.phx.gbl...
> > Hi All,
> >
> > What is a best way to empty (shrink) a database transaction log. I am
> > working with huge database - 10GB to 100GB- and basically doesn't care
> > about transaction log much when data is being deleted by a task. I have
a
> > deletion task consisted of many stored procedures which is running every
> > night. In some of the client's database, when those stored procedure
> > deleted data in different tables, the transaction log getting to big and
> SQL
> > appears to hang up. Sometimes a deletion task will take a whole
weekend.
> > Note that the stored procedures do use TRUNCATE TRANSACTION and CHECK
> POINT
> > trying to reduce the size of the transaction log.
> >
> > My question is, how can I tell SQL not to log the transactions when my
> > deletion procedures are running. If there is no way to to tell SQL not
to
> > log the transactions, is the a better way to control the size of the
> > transaction log file, other than using the CHECK POINT?
> >
> > Thanks.
> >
> >
>