Transaction Log Backup And Database Mirroring

Hi All,

I have the below scenario:

I have a database db1 which has been configured for database mirroring.

The log file is increasing big time and due to that I have setup transaction log backup job for every 6 hours and it will delete the trn files which are older than 2 days.

After doing this do I still need to shrink the database so that the log file is not increasing or what do I need to do In order to stop the log file from increasing big time.

Thanks in advance.
LVL 8
matrix_aashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Taking a Backup Log will not shrink your Log File for sure.
It will take a backup of all Active transactions allowing system to use those space further.

Hence you have to apply the command below to shrink the Log file and keep its size under control

DBCC SHRINKFILE ('ur_db_name', 1000)

where 1000 specifies the Size of your Log file.

Hope this helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
matrix_aashAuthor Commented:
Thanks rrjegan17 for the reply.

Shall I include the dbcc shrinkfile command in my job ?

How can I calculate the right size for my db ?

Thanks.

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Shall I include the dbcc shrinkfile command in my job ?

Yes.. you need to do it to keep the log file size in control

>> How can I calculate the right size for my db ?

That depends upon the frequency of this particular task. By default once you take your Full / Differential Backups active portions will be backed up leaving only the uncommitted transactions in your LDF File.

Then this would lead us to have a Log file size that would be sufficient between your Full / Differential Backup.

For eg, If you have Full Backup at 9.00 am and Differential Backup at 12.00 noon then your LDF file size should be a minimum of the growth that it may occur during these times.

Hope this helps
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

wiljeCommented:
No, you should not include a shrinkfile command in your job.  It is not something that should be done on a regular basis as it is going to cause file fragmentation.  For a transaction log, excessive shrinking and growing cause multiple VLF files to be created which will cause performance issues.
Backing up the transaction log on a regular basis will control the size of the log file.  It will grow as large as it needs to, to handle the number of transactions being processed between log backups.
I would recommend backing the transaction log up more frequently.  Every 6 hours is not often enough to recover from a disaster.  Every hour might be enough, depending upon your business requirements.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp 

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
wilje,
     Transactional Log backup job was already scheduled and to reduce multiple VLF files, I am requesting to set its initial size great so that it would avoid the creation of multiple Log Files.
One probable way for Multiple Log files is having your Initial file size very lower and your Autogrowth percent or file size value lower.
0
wiljeCommented:
rrjegan17:
The question was whether or not the OP should put an option to shrink the transaction log in his job.  You answered that yes - he should schedule a shrink file operation on a regular basis.
Shrinking files on SQL Server should never be done on a regular basis.  There are many reason why you should perform a one time shrink, but no reasons why it should be scheduled.  For example, when a very large transaction has occurred that you know is not ever going to happen again.  Then, you would shrink the log file back to the normal operating size.
 
0
matrix_aashAuthor Commented:
Then what is the best way to stop the increasing size of the transaction log.

Please let me know.

Thanks.
0
wiljeCommented:
The best way to stop the increasing size of the transaction log is to backup the transaction log more often.  I backup my transaction logs every 15 minutes on some systems, on others I back them up every hour.
Once you have this scheduled and running, you can then review the size of the transaction log backups.  I would wait at least a month to make sure I captured all types of processing that can occur (month end for example).  Once I have seen the largest backup size - I would then perform a shrink (one time) to a size a bit larger than the largest backup size.
I would then set the autogrow size to a percentage of that size.  For example, if I ended up sizing the log file at 1000MB - I would set the autogrow to 100MB.  Unless using that size would be smaller than a normal size of the transaction log backup.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Shrinking files on SQL Server should never be done on a regular basis.

It can be done on LDF / Transaction Log Files. And its not recommended to apply it on MDF / Primary data files.

If you do it on Primary Files, then it will increase the level of Fragmentation to your data. For your Log Files, once you do your Transactional log backup and backup log with truncate_only, the active portions are freed up and available for you. Hence if you shrink that portions to release memory to OS, it wont create a Fragmentation.

But this is not the case with MDF files and hence I never mentioned to do this for MDF files as your previous explanations holds good for MDF files.

Kindly revert if I need to explain more on this.
0
wiljeCommented:
Shrinking a file does not release memory to the OS.  I think you meant to say the shrinking frees up space on the drive.
I have to recommend against using BACKUP LOG ... WITH TRUNCATE_ONLY.  The TRUNCATE_ONLY flag has been deprecated and no longer works in 2008, specifically because people would use it without understanding what it does.
Using that command breaks the log chain and will prevent further transaction log backups until you have performed a full backup.  There are occasions where you need to clear a transaction log because it was not managed correctly, or a very unusual event has occurred.  In that case, you should perform the following:
  • ALTER DATABASE ... SET RECOVERY SIMPLE;  -- breaks log chain
  • DBCC SHRINKFILE(logical_file, size);  -- shrink the file back down to normal size
  • ALTER DATABASE ... SET RECOVERY FULL;  -- resets back to full recovery
  • BACKUP DATABASE...; -- start the log chain
  • BACKUP LOG ... -- backup transaction log
Again, shrinking of either data or log files should not be performed on a regular basis.  Backing up the transaction log on a frequent basis is how you manage the size of the transaction log, as well as what is required to meet your disaster recovery requirements.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I think you meant to say the shrinking frees up space on the drive.

This is what I meant by releasing memory to OS.

>> Using that command breaks the log chain and will prevent further transaction log backups until you have performed a full backup.

FYI, I have suggested to do that after a Full / Differential Backup.

>> The TRUNCATE_ONLY flag has been deprecated and no longer works in 2008

Agreed and since this question pertains to 2005, I have provided a workable solution.

>> Backing up the transaction log on a frequent basis is how you manage the size of the transaction log

I repeat it again, Regular Backup of your Transactional Log backup will not keep your Log File size in control and you have to perform Shrinkfile to keep the log file size in control.

FYI, asker has periodical Transactional Log Backups defined. And since his log file size is not in control, raised this question on how to achieve it.

You can reduce the frequency of Shrinkfile job but not remove that task in your Maintenance plan if you want to have log file size in control.

Hope this clarifies.
0
wiljeCommented:
If you perform a truncate only after performing a full backup, you have broken the log chain and will have to perform a full backup again to reinstate it.  That is just the way it works - don't take my word for it.  Test it out yourself.
And I will repeat this again - there are no good reasons for shrinking a log file on a regular basis.  If you have to worry about disk space usage where you are constantly shrinking a log file - you need to invest in more disk space.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> If you have to worry about disk space usage where you are constantly shrinking a log file - you need to invest in more disk space.

Then You need to calculate the maximum possible size for Log file and need to take Transaction logs at shorter intervals.
0
wiljeCommented:
>>Then You need to calculate the maximum possible size for Log file and need to take Transaction logs at shorter intervals.
Which is what I have been saying all along...
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
matrix_aash,

>> After doing this do I still need to shrink the database so that the log file is not increasing or what do I need to do In order to stop the log file from increasing big time.

Keep the Log file at a maximum possible size and then manually shrink files as required for the reasons suggested by wilje and don't include a maintenance task as I suggested earlier.

wilje,
    Hope we are on consensus on this aspect now.
Had a good discussion with you..
0
wiljeCommented:
rrjegan17: yeah, we are in agreement on this aspect now.  Good discussion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.