will this truncate the transaction log files?

[eja2000]

This is my transaction log bkp code;

i am backing up to the device 'xxxBackup'..
i also want to make sure that my transaction log files do not grow too large.i want to achieve this by truncating them after a backup.
can someone tell me if the below tsql will work and if not what code should i use..
thx



BACKUP LOG [adp2sql] TO [xxxBackup] WITH NOINIT , NOUNLOAD , NAME = N'xxx_log_bkp', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE

 

[SQLBill]

Why are you using NO_TRUNCATE? Take that out. Backing up the LOG will truncate it, but will not IMMEDIATELY release the free space.

If you need the free space released immediately, run DBCC SHRINKDATABASE or DBCC SHRINKFILE command. Refer to the BOL for more information.

-SQLBill

BOL=Books OnLine = SQL Server's Help
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Preposting information: FAQ481-4875 and FAQ222-2244 among others.

 

[eja2000]

i will take that NO_TRUNCATE part out..but when will the space be freed up?
is their a seperate job i need to free space?
thanks

 

[nigelrivett]

See

http://www.nigelrivett.net/TransactionLogFileGrows_1.html

Do you need the databases to use the full recovery model? Simple might save you any work.

The backup log will allow entries to be reused after the next checkpoint but to shrink the file you need to use dbcc shrinkfile

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[eja2000]

what i want to achieve is this;
1) i take full bkps every sunday
2) differential bkps every nite
3) log bkps every hour
4) i want to be able to restore the db to the closest point in time possible..
5) my recovery model is full
6) i want to keep my log files as small as possible by truncating them using bkps..


from what you have said above will this amended code achieve this;

BACKUP LOG [adp2sql] TO [xxxBackup] WITH NOINIT , NOUNLOAD , NAME = N'xxx_log_bkp', NOSKIP , STATS = 10, NOFORMAT

thanks for ur patience..i need to get this right

 

[SQLBill]

Yep, that should do it. Remember though, shrinking does not happen immediately after the log backup. Basically what happens is a log backup then moves the inactive parts of the log file to the end and from there they can be removed. It does happen, it's just not immediately after the backup.

-SQLBill