Hi Mark,
I have two jobs that I run periodically.
query_errvols.sql generates a list of volumes that were written by failed jobs. If a job failed, or was cancelled, it gets returned by this program.
query_orphan_volumes.sql does the same for volumes that are in the Media table with no corresponding JobMedia entry. (For our operations, we're also only interested in volumes in Pools with a PoolId between 2 and 5 inclusive.
They are online at http://s3.kmpeterson.com/special/query_errvols.sql and http://s3.kmpeterson.com/special/query_orphan_volumes.sql respectively. Hopefully even if these aren't useful immediately, they might show how you can query the tables for this kind of info.
Disclaimer: Writing SQL queries isn't on the A-List of things I'm known to be good at... there was considerable trial-and-error for me to come up with these. Please test in your environment before plugging them in!
(The shell script that uses these takes the list and for each volume runs 'echo "purge jobs volume=$VOL" | $BCONSOLE' against the list of volumes returned.)
Hope this helps, and I'd be grateful if there is an easier/more practical way to do this, or anything else anyone can add...
Happy Holidays!
_KMP
contact: http://kmpeterson.com/contact
On Thu, Dec 20, 2012 at 4:07 PM, <mark.bergman AT uphs.upenn DOT edu> wrote:
>
> I believe we have a significant number of tapes that bacula thinks are
> "full" but which no longer have jobs. These tapes were used normally
> and became full. Later we decommissioned the machines that were backed
> up to those tapes and pruned & purged the bacula jobs that referenced
> the decommissioned machines. The job & file records no longer exist in
> the database, but bacula still considers the tape to be "full", with a
> long expiration period (as set by the pool for full backups).
>
> Some of the tapes may also "store" failed backups, so the tape may have
> jobs, but the sum of the files on tape is zero bytes.
>
> We have no need to recover data from those tapes and want to re-use
> the media.
>
>
> Can anyone recommend a MySQL query to list all volumes that have zero
> jobs or zero files or jobs where the files sum to zero bytes?
>
>
------------------------------------------------------------------------------
LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
Remotely access PCs and mobile devices and provide instant support
Improve your efficiency, and focus on delivering more value-add services
Discover what IT Professionals Know. Rescue delivers
http://p.sf.net/sfu/logmein_12329d2d _______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
