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 |
|
cez
Starting Member
37 Posts |
 Posted - 2003-04-02 : 14:40:20
|
| Hi My log file is getting bigger and I desperate need a way to empty it .. and to avoid this in the future If you know, please tell me what can I do..Thx |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-02 : 14:50:09
|
| In database options set the recovery model to simple. This will truncate the log at checkpoint and stop[ it growing.To shrink it try dbcc shrinkfile (filename)==========================================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. |
 |
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-04-02 : 15:02:03
|
| What if he needs the full recovery? In this case you can write a job toBACKUP LOG db_name WITH TRUNCATE_ONLYfollowed by shrink and a full backupand schedule it to your needs.Bambola. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-02 : 18:13:42
|
| Shouldn't cez just backup the transaction log (assuming that he is using FULL recovery model) so that it frees up space inside the log? One should only run BACKUP LOG db_name WITH TRUNCATE_ONLY if the transaction log file is full. cez just said that it is getting bigger and not that he is encountering the error that says that it is full.cez,You need to figure out which recovery model best fits your application's needs. If you don't care about point in time recovery, then change it to SIMPLE. If you care about point in time recovery, then change it to FULL (it probably is already at FULL though), then create a job that backups the transaction log (but DO NOT include the WITH TRUNCATE_ONLY option because this does back it up, it simply truncates it) on a semi-frequent basis (we do ours every 15 minutes because we can only afford to lose about 15 minutes worth of data).Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-03 : 03:15:06
|
| I always assume that if someone wants to recover transaction logs then would have to know to back them up and would not have this problem.Hence always use a simple recovery model until you gain more knowledge.==========================================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. |
|
|
|
|
|
|
Empty log file