Solved

Purge logs of mirrored DB

Posted on 2013-11-03
10
322 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 22

Expert Comment

by:Patrick Bogers
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 78

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:Eugene Z
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
 
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:Eugene Z
ID: 39664409
try after trans log backup to shrink
0
 
LVL 42

Accepted Solution

by:
Eugene Z 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
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 walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…

756 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