ASDB SQL Database prune.

[TheMisio]

Hello,

Does anyone know how to get the BrightStor 11.5 SQL Database to prune its records after let's say 60 days?
Our databases have been growing feeling up the space. Also one of the databases having reached around 17 Gigs, got corrupted. I remember seeing once a SQL script created by CA to do just that. I cannot find it anywhere now.

Any help greatly appreciated.

Regards,

Michael

 

[vschumpy]

The prune job should run automatically - look for it in the job q. If it is not there, then add it using server admin.

 

[sanbe03]

You should consider to implement the maintenance plan of the SQl database :

http://supportconnect.ca.com/sc/redir.jsp?reqPage=search&searchID=TEC353603u

 

[TheMisio]

Thanks for your reply sanbe03.

Unfortunatley the link does not work at this time. I will try a bit later.

Regards,

Michael

 

[vschumpy]

Drop the 'u' off the end of the link

 

[TheMisio]

Nope!!!

Still not working.....

Regards,

 

[TheMisio]

OK!

Got there in the end.
I have set up the maintenance plan some time ago. But it does not delete any records. This plan only fixes minor problems and reindexes the tables and stuff. I've got a DB just two or three months old and already almost 18 Gig.....
I cannot run scheduled reports against it as it hangs in "waiting" stage and does nothing.....

Thanks for your replies...

Michael

 

[vschumpy]

The doc referenced does not cover how to remove records, just how to keep it in shape.

Have you checked that you have the prune job in the Job queue like I mentioned earlier, and that it is running without returning status "crashed"? The prune job is the will remove old records from the db. You should see xxx records pruned from database in the activity log if it is running.

 

[TheMisio]

Hi there,

We have got three backup servers. On each one of them, there is a database prune job. It runs every day, but it doesn't do anything. The job log is empty. No errors as well. I think those jobs only prune BAB based database not the SQL one.

Thanks a lot.

Michael

 

[vschumpy]

The prune job runs for whatever database is configured. You can always delete it and re-add it using server admin if it is doing nothing, or reporting status 'crashed'

 

[TheMisio]

Thanks vschumpy,

OK. If the DB is being pruned, why is it growing so large?
Problem is that I run scheduled reports every day (company requirements) but after few weeks the report job starts and then hangs at the "waiting" stage. I noticed it only happens on the big DBs. Is there any way to make sure the reports will be generated?

Regards,

Michael

 

[vschumpy]

Difficult to say for sure, but it will flatten out eventually, usually after around 30 days or so, unless you have irregular backup schedules or, for example rebuild machines with different server names a lot. I will explain why this is a factor if this affects you, but will not go into details at this point as it is kinda long to explain.

If it's hanging during report generation, this would normally point to either a resource issue (either Disk I/O or SQL has sucked up all your memory and CPU), or indicate that the indicies need to be rebuilt (which is what the SQL maintenance plan does). I would suggest this is run weekly on a Sunday (unless you run backups then).

Avoid running reports or maintenance when you know it will clash with the daily prune job. The prune job will usually be very disk I/O intensive (as it is when a merge is done), so running a report or maintenance at the same time is not advisable and can be counter-productive.

To debug this further, you could run tracing in SQL on the asdb database when the report is running. It would give you an idea of what SQL is doing at the time it 'hangs' and if it is a factor (it will show what SQL statements and T-SQL commands are being issued to SQL and if ARCserve is waiting on SQL, or whether ARCserve has just got itself in a mess.

There is an alternative to all the merging and pruning in that you could use Catalog DB. What this means is that although the session information will be available in the DB (and therefore it works with rotations), the actual session detail (files/directories/db's backed up) do not get automatically merged into the database, but are held on disk in compressed .cat form, and only merged to the DB if you actually require the detail for a point and click restore.

Catalog DB is an on/off thing configured in server admin, and I think there is a chapter on it in the admin guide - but don't quote me on it

See if anything from this longer than I planned post helps and we'll go from there.