November 6, 2008 at 8:01 am
One of my DB Maintenance job (It does reorg and take complete backup of user db) is failing for long times. It complains in event viewer that there is no space and then quits reporting OS Error 995. I understood that it is for resource crunch during that time and hence I rescheduled the job to another time when server was less loaded and it worked fine after a straight failure of 5 Months!!
Now business wants us to run this job on a particular day only and I am getting same issue. How can I figure out what will be the best time to run this job? I checked TSM and NT Backup schedule and they does not conflict with this one.
Also do you think is there any other root case behind this one?
Many thanks in advance.
November 6, 2008 at 8:28 am
How long does it take to run this job? Determine the time when there are least number of jobs running, then schedule this job. As you have tried already. Discuss with your management if this schedule is feasible and mention what extent of data loss would be incurred with this schedule you determine..
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
November 6, 2008 at 8:46 am
Last succesful run took around 4 hours. 🙁
I am talking with Managment to force the change
However, is there any way to trace the stress level for the server during that time? I want to avoid trace as it will probally chock the prod box. Also I am yet to get spotlight 🙁
November 6, 2008 at 8:48 am
You really have two different issues there, database backups and index rebuilds.
You should have some kind of backup happening on a regular schedule, along with transaction log backups if the database is in full recovery mode. A typical schedule would be to run a full backup of the database at night or during the period of least activity, and run transaction log backups every 15 minutes 25x7. You will have to design a plan that suits your situation, taking into consideration database size, disk space available, etc.
Re-indexing may not be the best thing for your situation. You might want to consider using index defrag instead. It is designed to be able to run while the database is in use. Re-indexing needs a lot of space in the database, because it moves the data to a new copy of the table, so you need to have as much free space in the database as your largest table. Index defrag does not need this, but it does need more space for transaction log backup files.
November 6, 2008 at 8:55 am
Thanks.. I do aggree with you but the client I work for has an inhouse app (which is actually a db backed by sp and history) to standarize these jobs.
So I can not really change the logic also.. Only I need to find a time when server can take this stress and run it smoothly. Please advice
November 6, 2008 at 8:59 am
Check the quite time, speak to your management in terms of money( the language management understands) then they will start to think about the change of startegy 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply