Solved

Purge logs of mirrored DB

Posted on 2013-11-03
10
300 Views
Last Modified: 2013-11-29
Our Citrix Xendesktop DB is mirrored between servers at 2 sites. As such the recovery model is set to Full. Even though we successfully run full backups on that DB each night, the logs for the database don't get purged and are 30GB for a DB that is about 100mb or less. FWIW we're using Netbackup.

How can i purge these logs without reducing our ability to fully restore if need be?
0
Comment
Question by:akalbfell
10 Comments
 
LVL 19

Expert Comment

by:Patricksr1972
ID: 39619904
Hi,

Quick way is to set recovery model to simple, run backup and turn back to full once finished. The logs will be gone.
0
 
LVL 8

Author Comment

by:akalbfell
ID: 39619968
Recovery model can't be set to simple for a mirrored DB
0
 
LVL 77

Assisted Solution

by:arnold
arnold earned 250 total points
ID: 39658050
You likely have an active transaction preventing the log from shrinking.
run
dbcc opentran(database)

on a mirrored DB, the transaction log does not actually shrink unless you have a setting to shrink logs/databases

You may want to setup a more frequent transaction log backup to manage the transaction log size.  i.e. if you backup once a day, then the transaction log will grow based on the number of transactions within 24hours.  if you perform, 2 or 3, 4 or more transaction log backups, the process of the transaction log backup will free up the space within the transaction log for new transactions.
100MB database with a 30GB transaction log may mean that you have a "service" that creates an active transaction that only clears on server reboot.
Find the transaction and you will be closer to solving your issue.

Had such an application at a site, a service monitoring a DB for events created a transaction that kept the transaction log growing and growing, until the service was restarted or the server was restarted.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 39658153
please clarify: are you talking about principal OR mirror db?

if principal-- make sure you are running trans log backups

for the mirror - just after trans logs of the principal DB made small and  after the new backup
was used to reset mirror db  - you will get small trans logs on the mirrored DB
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Author Comment

by:akalbfell
ID: 39662461
I am talking about shrinking the principal, not the mirror.
I have done transaction log backups which finish successfully but the file doesn't change in size.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 39664409
try after trans log backup to shrink
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 250 total points
ID: 39664423
and
you should set frequent trans log backups: every 15 -30 minutes.
this will help to keep trans log backup size

--
also if Trans log backup and shrink  log file is not helping,
check if anything using it by running  

select log_reuse_wait_desc from sys.databases where name = 'yourPrimcipalDBname'
0
 
LVL 8

Assisted Solution

by:akalbfell
akalbfell earned 0 total points
ID: 39672609
I tried a few things but still the log files haven't shrunk.

I disabled mirroring first of all
I tried running a full backup with the mode set to simple, that didn't work
I tried running a full backup with the mode set to full then a transaction log backup, that didn't work... Both times the jobs ran just fine but the logs still haven't shrunk.

Any ideas?
0
 
LVL 8

Author Closing Comment

by:akalbfell
ID: 39684950
Finally got it going. Ended up breaking the mirror, running some backups (which didn't seem to help immediately) then shrinking the logs. Then just had to rebuild the mirror.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
In 2017, ransomware will become so virulent and widespread that if you aren’t a victim yourself, you will know someone who is.
In this Micro Tutorial viewers will learn how to use Boot Corrector from Paragon Rescue Kit Free to identify and fix the boot problems of Windows 7/8/2012R2 etc. As an example is used Windows 2012R2 which lost its active partition flag (often happen…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now