Solved

Purge logs of mirrored DB

Posted on 2013-11-03
10
313 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 20

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 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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
 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
In this Micro Tutorial viewers will learn how to restore single file or folder from Bare Metal backup image of their system. Tutorial shows how to restore files and folders from system backup. Often it is not needed to restore entire system when onl…
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…

790 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