SQL Server Performance

Status field of job is not changing & sql agent is using 25% CPU

Discussion in 'General DBA Questions' started by suniljk7, Mar 17, 2008.

  1. suniljk7 New Member

    Dear all,

    I have one job for backing up all databases, till last day it wasworking fine. yesterday i noticed that the status of the job is notchange it was like ‘Executing step1’ but the backup all happened longbefore that. I checked by taking separate backups of each db it is alsoworking fine. Now I created a new job for backup of all db,also havethe same issue it took the entire backup but the status has not changedand also next run field also not changed. I couldn’t find anyerror anywhere in the logs. Now I noticed that sql agent is taking 25-30% of CPU. Please let me know your comments at the earliest.
    I am using sql server 2000 on windows 2003 server.
    John
    suniljk7, Mar 17, 2008
    #2

  2. ghemant Moderator

    ghemant, Mar 18, 2008
    #3

  3. suniljk7 New Member

    Thanks for the reply, i have the sysadmin rights and i can view the job history. for this the history is not updated with this las run (because the job says executing job) but all the back files has successfully created. if you take backup using EM or QA there is no problem, this happens only with the scheduled backup jobs or jobs are working fine. we can see this process in the process tab also i tried to kill this process but still its not ending.. after restaring the sqlagent the cpu usage will come down but the process executing job is still there..
    please let me know your findings..
    John

    suniljk7, Mar 18, 2008
    #4

  4. satya Moderator

    What kind of permissions the SQL Server services has on that drive or location where the backup is performed?
    Also have a thorough checkout of hardware disks in this case as it runs as a job it is taking longer time. For the HigH CPU ensure to check what other processes are running at the same time, such as DBCC statements.
    satya, Mar 18, 2008
    #5

  5. suniljk7 New Member

    The backup is happening on the local disk.. which administrator has full permission. i checked with dbcc opentran there is no open transaction. i have a doubt something has issue with sqlmaint in sql server 2000. i don't remember this if anyone know please let me know..
    Thanks
    John

    suniljk7, Mar 18, 2008
    #6

  6. satya Moderator

    How long this backup job takes to finish?
    How long it will finish when you execute from Query Analyzer?
    Do you see any warnings or error within Event viewer on that server?
    What is the service pack level on SQL ?
    satya, Mar 18, 2008
    #7

Share This Page