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 |
|
cipriani1984
Constraint Violating Yak Guru
304 Posts |
 Posted - 2009-09-28 : 07:18:18
|
| Hi,I run few SSIS packages every otherday of the week and sometimes I get the error transaction log is full.There isnt a need to keep the transaction logs in my case, whats the best way to truncate or shrink the logs?Am currently using the following code:- BACKUP LOG Purchases WITH TRUNCATE_ONLYwas just wandering for learning sakes, if this right thing to do? |
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2009-09-28 : 10:48:19
|
| In SQL server 2005 you can right click on the database. Choose Tasks/Shrink/FilesChange File Type: to Log. Then choose which Shrink Action you would like to do. Another way is to Detach the database, delete the log file and then re attach the database. Reattaching will create a new empty log file. You can alsu setup a maintenance plan that shrinks your log file. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-09-28 : 10:59:07
|
No, no, no. Don't shrink the log file.It will be expanded again later and this time it will most certainly be fragmented too.And if you use WITH TRUNCATE_ONLY, you break the backup chain."WITH TRUNCATE_ONLY" is also marked for deprecation, so no new development work should use this approach.Do a normal backup. Normal backups clears the log (not shrink).
N 56°04'39.26"E 12°55'05.63" |
|
|
|
cipriani1984
Constraint Violating Yak Guru
304 Posts |
Posted - 2009-09-28 : 11:01:11
|
So how do I perform normal backup?quote:
Originally posted by Peso
No, no, no. Don't shrink the log file.It will be expanded again later and this time it will most certainly be fragmented too.And if you use WITH TRUNCATE_ONLY, you break the backup chain."WITH TRUNCATE_ONLY" is also marked for deprecation, so no new development work should use this approach.Do a normal backup. Normal backups clears the log (not shrink).
N 56°04'39.26"E 12°55'05.63"
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-09-28 : 13:23:31
|
| I wouldn't bother with any of that. I would instead switch your recovery model to SIMPLE (right click on the database in SSSM, properties, options.) You just need your regular full backups when you use that recovery model. I only mention switching the recovery model since you say you have no need for the transaction log.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
|
|
|
|
|
Transaction log full