February 23, 2010 at 8:21 am
All,
Description:
As per scenario When ever any data is entered the logs of DMS operations goes to log file (ldf) and in buffer cache. Once the check point runs it will clear the buffer cache and write the data on the disk, simultaneously it will mark all the logs in ldf as Inactive.
Then come the lazy writer which is suppose to delete all the inactive data from the logs, however as the name states lazy writer it doesn’t have its own schedule hence to keep the size of the log file (ldf) under its threshold limits we need to first truncate the inactive logs by firing command “Backup log database name with truncate_only” and after that fire the command as “DBCC shrink file filename” to shrink the size of the ldf file.
Once done we need to take a full back up of database as following the above procedure will disturb the LSN number.
Question:
I have a set up of Log shipping. Now the size of the .ldf file is growing and i need to truncate that. How should I truncate and shrink the .ldf file? If I shrink the file using above mentioned command then I will also have to take a full back so that the LSN number remains in sequence. Will that not hamper my log shipping set up in any ways?
February 23, 2010 at 11:25 am
I am not sure where you got your information - but it is not correct.
First, that backup command is deprecated and no longer works in SQL Server 2008. Second, you don't manage transaction logs that way - you manage them by performing actual transaction log backups.
Please review the article I link to in my signature. It will explain how to manage transaction logs in great detail. I don't think your transaction log is growing because of log shipping. It is most likely growing because you are not backing it up frequently enough.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 23, 2010 at 11:29 am
You shouldn't need to truncate the log file. You should be able to manage that by taking log backups. Since you need to do that anyway for log shipping, that should solve your problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 24, 2010 at 3:14 am
Thank you for your reply Jeffrey.
However my question is with regards to SQL 2005.
My .ldf file size is growing and is crossing its threadshold limits. Now I need to truncate and shrink this file. If i do that I will have to take a full back up of my database so that the sequence of LSN number doesnt gets break.
If i do that my log shipping is getting breaked.
I wanted to know that without breaking log shipping how can i shrink the size of my .ldf file.
February 24, 2010 at 8:43 am
sunil.aggi (2/24/2010)
Thank you for your reply Jeffrey.However my question is with regards to SQL 2005.
My .ldf file size is growing and is crossing its threadshold limits. Now I need to truncate and shrink this file. If i do that I will have to take a full back up of my database so that the sequence of LSN number doesnt gets break.
If i do that my log shipping is getting breaked.
I wanted to know that without breaking log shipping how can i shrink the size of my .ldf file.
I don't understand what you mean when you say the size is crossing it's threshold limits. The transaction log is going to get as large as it needs to be to handle the number of transactions you have. If that is too large then you need to perform more frequent log backups.
Do not shrink the file just to allow it to grow again - it is a waste of time and effort.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 24, 2010 at 4:05 pm
Not only is shrinking just to let it grow again a waste of time and effort, it is also BAD. It leads to performance degradation and file fragmentation.
February 24, 2010 at 4:53 pm
agreed don't shrink the log unless you really have no choice.
just to answer your questions:
truncating the log will break the LSN chain and therefore break log shipping. However a log backup will clear out the inactive portion of the log so there is no need to truncate the log.
you can shrink the log of a database participating in log shipping without affecting logshipping. Shrink does not break the log chain.
Because you can does not mean you should though. 🙂
---------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply