how to do automatic backup for transaction log and truncate it?

Hi,
For example, i have set my log files to 2G maximum, if
reach 1.5G, i want the sql server to help me truncate the
log automatic. Can anyone help me?
Thanks a lot
regards,
florence
You could add a second step to your transaction log backup job that runs once
the backup step runs successfully:
USE Database_Name
GO
DBCC SHRINKFILE (Log_File_Name, 0)
GO
"florencelee" wrote:

> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence
>
|||hi..
create a Backup device for temp usage.. ( example name .. logresizebackup
Create Proc master.dbo._resizemydb
as
backup log ecatalog to logresizebackup
create a job that runs master.dbo._resizemydb dont shedule
Create an Alert ..
type must be : SQL Server Performance Condition alert
select SQL server databases as Object
select Data file(s) size as counter
select DB name instance as
and alert if Counter must be rises above and give the value of 15000000
then
in to the response tab.. Select execute Job and Select our Job that ll
execute master.dbo._resizemydb
thats it
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:09e401c4ac62$6165a3c0$a301280a@.phx.gbl...
> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence
|||If you only want to truncate the log and not retain the information that
will allow you to do point in time restore you might want to think about
putting the recovery mode to SIMPLE. Then it will truncate the log when it
reaches 70% full which is pretty close to what you are after.
Andrew J. Kelly SQL MVP
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:09e401c4ac62$6165a3c0$a301280a@.phx.gbl...
> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence