Shrinking the Transaction Log while Logshipping is Configuration
Question
Monday, January 2, 2012 7:25 AM
Hi,
I have logshipping configured between my Prod Servers and DR Servers. Transaction log backup,copy and restore takes place every 5 min.
While configuring logshipping on these servers I had shrinked the LDF file of the prod database whilst it was in simple recovery mode. Then I converted it into full recovery mode and configured logshipping on it. However now I found out that the log on the prod database has increased!.....Can i possible shrink the LDF file? I understand that in general if a db is on full recovery mode, shrinking is only possible after we take a log backup which commits the uncommitted transactions. In my case I assume that since my logs are backup-ed regularly shrinking is possible. Am I right in this scenario....Pls help
Thanks and Regards,
Tauhid S Shaikh
All replies (10)
Thursday, January 5, 2012 4:10 AM âś…Answered
Hi Tauhid,
As pointed, it is not a good approach to shrink the log file to reduce the size. If you really want to shrink in this scenario, please use NOTRUNCATE option. For more information: How to shrink Log File when Log shipping is implemented on database ?
This thread may be helpful to you: log file shrinking.
Stephanie Lv
TechNet Community Support
Monday, January 2, 2012 7:29 AM
Why do you need to shrink the log? Generally it is possible but I would not recommend doing it.Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Monday, January 2, 2012 8:25 AM
Hi Uri,
I need to shrink since the log files are getting huge.
Thanks and Regards,
Tauhid S Shaikh
Monday, January 2, 2012 8:29 AM
So, it is just short time solution, you can still backup log file every 5 minutes but delay copy and restore operationsBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Monday, January 2, 2012 1:04 PM
only way to shrink log file would be configure log backup that is the only way to mark in-active portion of log file to re-use other wise it will grow and causes space issueshttp://uk.linkedin.com/in/ramjaddu
Thursday, January 5, 2012 3:12 PM
Thanks a lot Stephanie....... :)
Your reply helped me a lot indeed
Thanks and Regards,
Tauhid S Shaikh
Friday, August 10, 2012 2:50 PM
Why is shrinking the transaction log a bad idea?
Suppose you have a server with 20 user databases in it. Each of the databases is being log shipped. Normally the transaction logs stay around 50 mb in size. On a regular basis however, the applications - at different times - generate a burst of DML that causes it's database's log to grow to 10gb! If you don't shrink them, at some point you're going to have nearly 200 gb of wasted space. In such a case isn't it better to shrink the log files after the backup job runs?
I wish SQL Server had a built in option to specify to shrink the log file every time the LS Backup job ran. I'd set it and forget it. You can't use the auto-shrink feature (that I'm aware of) because it will also shrink the mdf file and I don't want it to do that and fragment all my indexes.
Instead I need to create my own job that checks for oversized log files and shrinks them back to normal. IMO this ought to be an option for the LS Backup job, or at the database level (but ONLY the tx-log, not all database files).
Also isn't NOTRUNCATE ignored for log files?
Chuck
Friday, March 22, 2013 12:30 AM
If you really want to shrink in this scenario, please use NOTRUNCATE option. For more information: How to shrink Log File when Log shipping is implemented on database ?
However, BOL states, "[NOTRUNCATE is applicable only to data files. The log files are not affected.]( "DBCC SHRINKFILE (Transact-SQL)")"!
Monday, August 29, 2016 10:22 AM
Hi Guys,
As i am checking that file size doesn't reduce to use NOTRUNCATE as per below reference.
NOTRUNCATE
Moves allocated pages from the end of a data file to unallocated pages in the front of the file with or without specifying target_percent. The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when NOTRUNCATE is specified, the file appears not to shrink.
NOTRUNCATE is applicable only to data files. The log files are not affected.
This option is not supported for FILESTREAM filegroup containers.
Reference Link: https://msdn.microsoft.com/en-IN/library/ms189493.aspx
So please clear it.
Awaiting you reply!
Thank You!
Tuesday, August 30, 2016 4:18 AM
hello ,
you are correct!
Also Aware.
NO_TRUNCATE
it is used in the Log backup option,in case if My database is damaged(suspect) so this option allows to take the log backup(it might have the incomplete data as well).
Also NO_TRUNCATE is similar to the COPY_ONLY and CONTINUE_AFTER_ERROR.
yes you can use the below option for shrink file-
TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
target_size is ignored if specified with TRUNCATEONLY.
The TRUNCATEONLY option does not move information in the log, but does remove inactive VLFs from the end of the log file. This option is not supported for FILESTREAM filegroup containers.
But dont use it,it breaks the log chain.
ref-https://msdn.microsoft.com/en-IN/library/ms189493.aspx
Note - Dont use the TRUNCATE & NOTRUNCATE without knowing what it mean for.
Even you have dangerous command that exist TRUNCATE_ONLY for the log backups --but it breaks the log chain
For your question-
Then I converted it into full recovery mode and configured logshipping on it. However now I found out that the log on the prod database has increased!.....Can i possible shrink the LDF file?
>>Yes you can shrink the log file as it is in logshipping ,but go through the my words below in this thread below-
Why shrinking?
>> your next log backup will know what to do,it will clears the inactive vlfs and help to reuse them,so I dont think so shrinking is required for the log file,so incase if you are going to do then - you are not managing the log file properly.
if the log file is not in huge space occupied then ignore of shrinking -I would say.
Also the best good option would be schedule frequent log backups in logshipping.
ofcourse one time but dont use it regularly ,urging not do .. if any space issues exists ensure what queries causing the log file to use it and see if that can be tune with chunk wise,if that didn't help lookout for expand the drive space.
if you have any further question shoot me- Iam here.
Also you would have been done shrinking before you configured logshiping at the first place isn't it?.
Note - some of the features already deprecated,before you post give the edition and version details for sql.
Regards, S_NO "_"