Truncate logfile on a specific time

Question

Truncate logfile on a specific time

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

You might want to read this article[/url] by Gail Shaw of why you shouldn't truncate logs.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 2

abemby (4/13/2010)
Hi Adam
I did mention that this is done after database is backedup... i completely agree with you... may be i should have highlighted it in bold.
... >> A .bat file which is scheduled, on the server, to run every weekend after the database is backedup ...
To add here - there is no activity in between the backup and shrinking - this ensures that you have a backup of the latest dataset to start the new log...
Also due to space contraints, as you have mentioned, destroying is necessary
bigger and better hardware infrastrutcure might not need this - but its not that bad as you have pointed out.
And finally not to take the credit from you 🙂 - posted to give a full cycle of the job
cheers

Actually, this order is backwards. You should shrink the log file BEFORE the backup, as doing it afterwards invalidates the log file for transaction log restores. Again, see Gail's article.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Eduard SSCommitted Points: 1761 More actions · Answer 3

perhaps a short explanation how the log file internally work solves your question. i left out some technical details though.

the log file is a chain of virtual log files. you could consider it a ring / circle structure. if you have 10 vlog's, you start using 1, then 2, then 3 etc. at point 5 some actions occur with the result that vlog 1 and 2 are marked as unused again. so the room can be used again for logged operations. the counter goes to 6,7,8,9,10. 1,2 were marked as unused so the proces reuses 1,2.

if all vlog's are in use the log file is made larger to add new vlog's.

after a while the size of the log file stabilizes.

the total size (assuming autoshrink turned off) will not get smaller. it is possible to have a log file with over 90% unused at certain moments of the day.

"some actions" are backup log and the checkpoint process. these actions make vlog space "unused" again and available again.

so if this cycle happens every day there is no "4 week old information". the used space is freed inside the log file so no need to empty it yourself, you cant even.

the only action you can do, and there are several posts on sql serverl central on this part, is shrink your db's. in general this is discouraged.