Post reply

Backup agents hanging

  • January 9, 2007 at 12:16 pm

    #193838

    Transaction log and database backup jobs are hanging on our v2000 SP4 server. I've stopped the jobs only to have them hang again the next time around. One job is part of a maintenance plan, one is not. I've studied the previous posts, including the very recent one, without seeing the solution...maybe because I am new to this.

    It appears the backups themselves are finishing (log says they completed). I checked out the sysprocesses table per a previous post: the db backup job seemed to be stuck at DBCC, the log backup was stuck at cmd 'execute'. Unfortunately I don't know what to do with this information, what is the next course of action? Thanks very much.

  • January 10, 2007 at 4:58 am

    #682336

    Hi,

    1. Check the SQL Server error log for complete information if the backup is completed ? If not then what is the exact error message?
    2. Check the SQL SERVER agent log SQLAGENT.OUT in folder C:\Program Files\Microsoft SQL Server\MSSQL\LOG for any error
    3. Check the view job history for detail.
    4. Is this happened for the first time?

     
     

    Minaz Amin

    "More Green More Oxygen !! Plant a tree today"

  • January 10, 2007 at 10:32 am

    #682460

    How big is the database ? Double check your maintenance plan (teh Integrity tab). You probably do not want to run a DBCC CHECKDB every time before you backup a transaction log. 

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • January 10, 2007 at 11:24 am

    #682495

    Okay, let's create a checklist.

    1. Which one is a maintenance plan and which is a user-created job?

    2. Is the transaction log backup job trying to backup databases that are in SIMPLE mode? Is it trying to backup MASTER?

    3. What time(s) are both of the jobs scheduled for? Do they ever run at the same time?

    4. How long does each job take to run? Does the run time from one job overlap with the start time for the other job?

    -SQLBill

  • January 12, 2007 at 8:38 am

    #682932

    There are external services (GIS) accessing the databases. I cycled those off/on and was able to get the agents to work, but now other agents (Optimiz. and TLog bkup) for other databases have hung, so I expect my original issue to return. Here's the answers to the above questions and some new observations.

    -Database sizes in general are small, < 50MB, with one DB at 1.6GB.

    -No errors are being reported.

    1. The differential backup is user-created. It includes a DBCC shrinkdatabase after the backup. The TLog backup is a Maint plan. (Optimization is Maint plan as well)

    2. The databases are Full recovery and master is not being backed up (that's done w/separate agent).

    3. There is one time slot that the Tlog and Diff backup happen at the same time, I will fix this.

    4. Jobs are short-15 min or less-so they shouldn't overlap (except for #3...).

    I've now discovered the Activity monitor...my new problem started with the Optimization agent hanging. DBCC seems to be getting blocked by a Select command from yesterday. My questions are:

    a. Shouldn't optimization work regardless of database activity?

    b. Would a hung optimization cause a Tlog backup (scheduled an hour later) to hang as well?

    c. Should I be focusing more on the external services and less on SQLAgents?

    Kind Regards,

    Elke

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply