| Author |
Topic |
|
sql777
Constraint Violating Yak Guru
314 Posts |
 Posted - 2003-09-30 : 20:54:54
|
| Hi,My database is less than 50 mb yet my log file is 5000+ megs.I do a complete full backup and I don't have to worry about logs since I can do a full-recover if needed. (my scenerio doesn't require a very precise backup capability)How can I trim this log down completely? Since I don't care for recovery etc since I can do a full-backup, its safe to say I should keep my log file very small correct? (i've seen in EM I can limit the size of it, should I? any drawbacks?)Thanks! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-09-30 : 22:18:54
|
| Use BACKUP LOG myDatabase WITH NO_LOG to truncate the log. Although it really wouldn't hurt to do regular log backups anyway, just in case. Your "scenario" may need a little more precision some day, and it really hurts when you don't have it. |
 |
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2003-10-01 : 05:45:24
|
| Hi,What funtionality will the logs give me? If I need to roll-back an operation that I performed?Sorry for the newbieness. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-01 : 08:05:32
|
| Yes. Transactions are recorded in the log, and are use for rollback and rollforward operations. It ensures that all data changes are recorded in the event of a system failure, so that data integrity can be maintained. |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2003-10-01 : 10:47:29
|
| Yeah I'm in no need of it.So I just pop into QA and type : BACKUP LOG myDatabase WITH NO_LOG This will not affect a live database? Give me the goahead pls!I'll try looking this up in BOL, but I want some human approval also hehe |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2003-10-01 : 11:10:11
|
| If you just want to just truncate the log without taking a backup.( you can usedump transaction databasename with no_LOGHaving said that, i will recommend Rob's suggestion.-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-01 : 11:16:56
|
quote:
Originally posted by sql777
Yeah I'm in no need of it.
Those be clouds on the horizon, matey...prepare to batten down the hatches...I'd suggest you set up a maint. plan and schedule it to run periodically...What's your recovery model?Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2003-10-01 : 11:33:24
|
| I just do a fullbackup every month. The data is not critical at all! I don't need incremental recovery capabilities, I know what you guys are saying but this is not critical data!!!! hehe.Ok so: dump transaction databasename with no_LOG that's it? its safe for live db? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-01 : 11:48:24
|
| Still want to know what your recovery model is...sounds like full..But how do you get a 5gb log from 50mg of data?Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2003-10-01 : 12:13:37
|
| Hi Brett,Yes its a full backup model that I do periodically.I'm not sure, i've heard that the TLOG can grow to large amounts. I've done allot of data moving around so I guess it adds up over the months...*shrugs* |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-01 : 12:46:52
|
| You need to change your recovery model to SIMPLE. Go to your database in Enterprise Manager, right click on it, go to properties, then go to options tab. Yours is probably set to FULL for recovery model, which is not what you want. It is what is causing your problems. If you are not going to backup the transaction log, then change it to SIMPLE.Tara |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2003-10-01 : 14:29:46
|
| ahh...i see, that's what you meant. I thought you meant when you guys asked me "what is your rec. model", I thought you meant what my personal backup stragey/model was? hahahahaHope this works! |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2003-10-01 : 14:34:13
|
| Ok I changed it to simple.Can or should I delete that huge tlog file now? The database is now huge, but it has 5000 megs free or not used. How can I shrink it?thanks for your advice guys. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-01 : 14:41:42
|
| You can NOT delete the LDF file. You will need to shrink it in order to get the file size down. Or, you could also detach the database and then use sp_attach_single_file_db so that it creates a very small LDF file for you (after you detach it, you would need to delete the LDF file then run the stored proc). But for this method, it would take the database down temporarily. If you can not afford that, then take a look at DBCC SHRINKFILE in SQL Server Books Online. You would need to shrink the log file.Tara |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2003-10-01 : 14:59:56
|
| Thanks! I used: DBCC SHRINKFILE (MYDB_Log)Now my hard drive can breath again, thanks again for all your great SUPPORT! |
|
|
|
|
obese log file