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 |
|
jrunowski
Starting Member
8 Posts |
 Posted - 2006-11-27 : 13:01:59
|
| Hello, I have a few questions about LDF files, due to some issues i am having with them. I first noticed the issue when my 500 Gig local disk had low disk space warnings. I found that one of my database LDF files was over 470 Gigs. This database is only an archive db that is rarely if ever used. My first question is how can a log file get this big and why does it never "purge" itself? My second question is how can i get this log file to "purge" on a daily basis? I have read about placing the database recovery mode in SIMPLE, will this clear it out? My last question is how come some of my other larger databases have small log files while this one is abnormally large?Sorry for the newbie question i am just unclear as how LDF transaction logs purge themselves.- Jesse |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-27 : 13:07:04
|
| Take regularly backups.Or truncate the file manually in EM.Peter LarssonHelsingborg, Sweden |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-11-27 : 13:10:31
|
| use simple recovery mode if you do not need point in time recovery. To be clear - If you can handle losing data up to the last full (or differential) backup then simple recovery mode is for you.Using simple recovery mode eliminates the need for transaction log backups.-ec |
|
|
|
jrunowski
Starting Member
8 Posts |
Posted - 2006-11-27 : 13:50:04
|
| Just to summarize, transaction log files will not be cleared unless they are backed up or manually truncated. If i place the database in Simple mode transactions will not be recorded, which is why i would have to recover from the last full. If i place the database in the simple recovery mode will that clear the 470 gig LDF file?Thanks for the replies! |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-11-27 : 14:36:09
|
quote:
Originally posted by jrunowski
Just to summarize, transaction log files will not be cleared unless they are backed up or manually truncated. If i place the database in Simple mode transactions will not be recorded, which is why i would have to recover from the last full. If i place the database in the simple recovery mode will that clear the 470 gig LDF file?
slight correction - in simple mode transactions are written to the tlog as usual. However, the tlog is truncated at checkpoint.Here is a good overview of how this works http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_8y3y.asp-ec |
|
|
|
jrunowski
Starting Member
8 Posts |
Posted - 2006-11-27 : 15:05:42
|
| Oh ok thanks EC |
|
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
|
|
Abnormal .LDF file grow