Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
 Posted - 2002-10-09 : 16:16:32
|
| How does one go about forcing a TLOG to closeout all transactions after a backup?How do you force close out of active transactions? Would an open - sleeping connection affect the truncation of the transaction log?Edited by - ValterBorges on 10/09/2002 16:17:56Edited by - ValterBorges on 10/09/2002 16:24:40 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-10-09 : 16:31:22
|
| The effect of a backup on the transaction log is to mark the last transaction not serialized to secure storage ( the LSN ). It does not affect the status of active transactions, nor to my knowledge is there an automatic way to, since that act would run contrary to the design of the transaction log.Jonathan{0} |
 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-09 : 17:40:20
|
| Why then would a transaction log backup not be truncating the transaction log? |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-10-09 : 17:49:07
|
| A transaction log backup does not truncate the log, it only marks the point where the last backup was taken. If you want to truncate the log, you will need to do a full backup and then run the following command:BACKUP LOG database_name WITH TRUNCATE_ONLYThis will truncate the log and then you can use transaction log backups.*************************Someone done told you wrong! |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-09 : 19:33:05
|
| I run full backup every day,a differential backup every hourand a transaction log backup every 15 minutesI'm trying to minimize the size of the transaction log becauseit is growing larger in 15 minutes that a full backup and I don't know how?BOL says:"After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE"it also says:"Truncating the Transaction LogWhen SQL Server finishes backing up the transaction log, it automatically truncates the inactive portion of the transaction log. This inactive portion contains completed transactions and so is no longer used during the recovery process. Conversely, the active portion of the transaction log contains transactions that are still running and have not yet completed. "Can someone shed some light on this. What is correct the order?Is it possible that a Transaction Log grows for reasons otherthan an insert, an update, or a delete?Edited by - ValterBorges on 10/09/2002 20:12:01 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-09 : 20:20:56
|
| INDEX or STATISTICS creation/updates are also logged if you are running in Full Recovery mode. DBREINDEX/CHECKDB/CHECKTABLE statements also log a great deal of stuff in Full Recovery, lots of people have posted questions about why their maintenance plans grow the log so much, and this is usually the cause.You might want to try running the CHECKPOINT statement before you perform a backup and see if it helps clean up the log. Also, if it is not imperative to keep the database available during backup, set it to read-only and/or single-user mode before you do the backup. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-09 : 21:05:29
|
| I found out what's making the tlog grow so quickly.We have a dts that imports lots of data every 15 minutes.Thanks guys. |
|
|
|
|
|
|
|
|
Force TLOG To Clear after Backup