SQL Server Performance Forum – Threads Archive
Regd Truncating T- Log
Hello , I want to truncate the T -log file every weeekend. I want to know if my plan is right. I have T – log backups from 7.00 am till 9.00 pm every day of the week. On saturday , I do the Re-organize of the Index of all the databases starting at 10.00 pm and truncate the log file . And the full database backup is taken at 12.30 am. In this process do , I need to take any log backup before I take the full backup and do I lose any data when I truncate the log file after the index re-organize . Thanks in advanceDo you want to truncate or shrink the log? If you want to shrink, better reconsider the decision. For explanation read the next thread, especially hoo-t discussion:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6894 To reduce log growth, better reindex just fragmented indexes. See next article for details:http://www.sql-server-performance.com/tp_automatic_reindexing.asp
It applies to sql server 2000 but it could work on 2005.
Thanks for the explination. I did not get a clear difference between shrinking of log file and truncating of log file. And which one is suggested. As I have read the shrink of log file reduces to a certain size specified and also truncate does the same..so I am a little confused. I need to do any of them to reduce my log file on a weekly basis, as its about 200% the size of the data file. Thanks
Vaddi
Take your time to read thru the post referred by Mmarovic and this has been discussed here many times, so also refer thru other posts about the topic. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Thanks all. I am going through all the related topics. One last question I have is , is it normal the log file to be 100% or 120% the size of the data file. Thanks
It is not normal being more than size of data file, or even depending upon the jobs, processes on your SQL database it might be even bettern to keep it 100% of data file size. But at the same time rather than increasing and decreasing on a timely basis, keep a value to Trnasaction log size. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
quote:Originally posted by vaddi
I did not get a clear difference between shrinking of log file and truncating of log file.
Shrink returnes the space to the OS, truncating marks log space, occupied by completed transactions, free for reuse.
I did not get a clear difference between shrinking of log file and truncating of log file.
quote:
I need to do any of them to reduce my log file on a weekly basis, as its about 200% the size of the data file.
You should not reduce on weekly bases. When you stop reindexes all indexes at once sql server will not need as much space. You may add transaction log backup after rebuild of each index (assuming recovery model is full). If you do so, next index rebuild can use the space used by previous rebuild and marked free for reuse by tran log backup. If you do so, then it makes sense to reduce the size of transaction log somewhat (see Satya’s recommendation), but only once, not on weekly basis. To do it once you can use the script mentioned here:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8018&whichpage=1I need to do any of them to reduce my log file on a weekly basis, as its about 200% the size of the data file.
quote:Originally posted by vaddi
Hello ,
I want to truncate the T -log file every weeekend.
I want to know if my plan is right.
I have T – log backups from 7.00 am till 9.00 pm every day of the week.
On saturday , I do the Re-organize of the Index of all the databases starting at 10.00 pm and truncate the log file . And the full database backup is taken at 12.30 am.
In this process do , I need to take any log backup before I take the full backup and do I lose any data when I truncate the log file after the index re-organize .
Thanks in advance
what you must understand is that manual truncation breaks the log backup chain. The first action after the truncation of Log is to be either the full or differential backup of the database. You must read and understand about the backup architecture of SQL Server. You have mentioned that you do take full backup after trancation of Log , so you are in right track and need to take Log backup after Trncation of Log file. What I would say is , **if possible** you should take full (or even Differential) backup of your database frequently. I repeat *if possible*, the advantage of Full backup is that the restoration become easy and fast. this topic has been discussed n number of times and you can refer that threads also. Transaction Log Links http://msdn2.microsoft.com/en-gb/library/ms190440.aspx
http://msdn2.microsoft.com/en-gb/library/ms179355.aspx
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19093 Madhu
]]>