Loading ...
Sorry, an error occurred while loading the content.

Re: : forcefully kill the sql job

Expand Messages
  • Madhu K Nair
    If you kill a process (from Management studio or using t-sql KILL ) system has to rollback the transaction to get the database into a consistant stage. There
    Message 1 of 9 , Jun 1, 2008
    • 0 Attachment
      If you kill a process (from Management studio or using t-sql KILL )
      system has to rollback the transaction to get the database into a
      consistant stage. There is no other way to do this, you have to wait
      till the process is rollbacked.

      Madhu

      --- In sqlcon@yahoogroups.com, Deepak Garg <deepakgrg@...> wrote:
      >
      > Hi All,
      >
      > Is it possible to kill the SQL Agent job forcefully. Because on
      production server its not possible to restart the sql serverice. Some
      of sql jobs used to hand during the different steps, on intirgity
      check, full backup steps etc.
      >
      >
      > I used to kill them from process info but it takes no of days
      sometime to roll back and i didnt find any other option except to
      restart the service of sql. so looking for some idea from you guys.
      >
      > I usually faced this problem in sql 2000.
      >
      >
      > Thanks in advance
      >
      > Cheers,
      > Deepak
      >
      >
      >
      > [Non-text portions of this message have been removed]
      >
    • sunil john
      Dear Deepak, we also faced the same issue on sql 2000 and solved by restarting sql agent. Best Regards Sunil John ... From: Madhu K Nair
      Message 2 of 9 , Jun 1, 2008
      • 0 Attachment
        Dear Deepak,

        we also faced the same issue on sql 2000 and solved by restarting sql agent.

        Best Regards

        Sunil John



        ----- Original Message ----
        From: Madhu K Nair <madhuotp@...>
        To: sqlcon@yahoogroups.com
        Sent: Monday, June 2, 2008 7:13:20
        Subject: MUGH-SQLCON Re: : forcefully kill the sql job


        If you kill a process (from Management studio or using t-sql KILL )
        system has to rollback the transaction to get the database into a
        consistant stage. There is no other way to do this, you have to wait
        till the process is rollbacked.

        Madhu

        --- In sqlcon@yahoogroups. com, Deepak Garg <deepakgrg@. ..> wrote:
        >
        > Hi All,
        >
        > Is it possible to kill the SQL Agent job forcefully. Because on
        production server its not possible to restart the sql serverice. Some
        of sql jobs used to hand during the different steps, on intirgity
        check, full backup steps etc.
        >
        >
        > I used to kill them from process info but it takes no of days
        sometime to roll back and i didnt find any other option except to
        restart the service of sql. so looking for some idea from you guys.
        >
        > I usually faced this problem in sql 2000.
        >
        >
        > Thanks in advance
        >
        > Cheers,
        > Deepak
        >
        >
        >
        > [Non-text portions of this message have been removed]
        >



        Send instant messages to your online friends http://uk.messenger.yahoo.com

        [Non-text portions of this message have been removed]
      • Deepak Garg
        Thanks Sunil and Madhu for your response on it. Adding to my below problem. The job used to hang every day and takes no of days (i notices over weekend) to
        Message 3 of 9 , Jun 4, 2008
        • 0 Attachment
          Thanks Sunil and Madhu for your response on it.

          Adding to my below problem. The job used to hang every day and takes no of days (i notices over weekend) to killed/rollback. yesterday i ve restarted sql service to flush it and taken manually backup from EM it has taken approx 1 and half hour to complete(size of DB is approx 74GB. The job again started with its scheduled time in the late evening and was running on its database backup step till in the morning (means again got hang - so i killed and again :( )

          Please note:

          1) I am using xp_sqlmaint in my job while other system database jobs are using it and getting success everyday on the same server.

          2) The drive has enough space for the backup (so no space issue)

          I don’t know if anything else can be checked at my level,

          Please suggest if anyone have any clue or suggestions about it.

          Thanks in advance.

          Regards,
          Deepak

          sunil john <suniljk7@...> wrote:

          Dear Deepak,

          we also faced the same issue on sql 2000 and solved by restarting sql agent.

          Best Regards

          Sunil John

          ----- Original Message ----
          From: Madhu K Nair <madhuotp@...>
          To: sqlcon@yahoogroups.com
          Sent: Monday, June 2, 2008 7:13:20
          Subject: MUGH-SQLCON Re: : forcefully kill the sql job

          If you kill a process (from Management studio or using t-sql KILL )
          system has to rollback the transaction to get the database into a
          consistant stage. There is no other way to do this, you have to wait
          till the process is rollbacked.

          Madhu

          --- In sqlcon@yahoogroups. com, Deepak Garg <deepakgrg@. ..> wrote:
          >
          > Hi All,
          >
          > Is it possible to kill the SQL Agent job forcefully. Because on
          production server its not possible to restart the sql serverice. Some
          of sql jobs used to hand during the different steps, on intirgity
          check, full backup steps etc.
          >
          >
          > I used to kill them from process info but it takes no of days
          sometime to roll back and i didnt find any other option except to
          restart the service of sql. so looking for some idea from you guys.
          >
          > I usually faced this problem in sql 2000.
          >
          >
          > Thanks in advance
          >
          > Cheers,
          > Deepak
          >
          >
          >
          > [Non-text portions of this message have been removed]
          >

          Send instant messages to your online friends http://uk.messenger.yahoo.com

          [Non-text portions of this message have been removed]







          [Non-text portions of this message have been removed]
        • Kuldeep Chauhan
          If your job is hanging quite often then of course there would be some genuine problem. Can you please share the information listed below 1. Which SQL Server
          Message 4 of 9 , Jun 5, 2008
          • 0 Attachment
            If your job is hanging quite often then of course there would be some genuine problem. Can you please share the information listed below

            1. Which SQL Server version you are using? (Enterprise, Standard etc.). I believe you are running SQL Server 2000.

            2. What is the current service pack applied on box? (SP3, SP4 etc.). Plus provide the Product Version, for example 8.00.3161 etc.

            3. If possible, share what steps your job is performing? is it only backup or some other maintenance tasks along with it.

            Thanks,
            Kuldeep

            Deepak Garg <deepakgrg@...> wrote:
            Thanks Sunil and Madhu for your response on it.

            Adding to my below problem. The job used to hang every day and takes no of days (i notices over weekend) to killed/rollback. yesterday i ve restarted sql service to flush it and taken manually backup from EM it has taken approx 1 and half hour to complete(size of DB is approx 74GB. The job again started with its scheduled time in the late evening and was running on its database backup step till in the morning (means again got hang - so i killed and again :( )

            Please note:

            1) I am using xp_sqlmaint in my job while other system database jobs are using it and getting success everyday on the same server.

            2) The drive has enough space for the backup (so no space issue)

            I don’t know if anything else can be checked at my level,

            Please suggest if anyone have any clue or suggestions about it.

            Thanks in advance.

            Regards,
            Deepak

            sunil john <suniljk7@...> wrote:

            Dear Deepak,

            we also faced the same issue on sql 2000 and solved by restarting sql agent.

            Best Regards

            Sunil John

            ----- Original Message ----
            From: Madhu K Nair <madhuotp@...>
            To: sqlcon@yahoogroups.com
            Sent: Monday, June 2, 2008 7:13:20
            Subject: MUGH-SQLCON Re: : forcefully kill the sql job

            If you kill a process (from Management studio or using t-sql KILL )
            system has to rollback the transaction to get the database into a
            consistant stage. There is no other way to do this, you have to wait
            till the process is rollbacked.

            Madhu

            --- In sqlcon@yahoogroups. com, Deepak Garg <deepakgrg@. ..> wrote:
            >
            > Hi All,
            >
            > Is it possible to kill the SQL Agent job forcefully. Because on
            production server its not possible to restart the sql serverice. Some
            of sql jobs used to hand during the different steps, on intirgity
            check, full backup steps etc.
            >
            >
            > I used to kill them from process info but it takes no of days
            sometime to roll back and i didnt find any other option except to
            restart the service of sql. so looking for some idea from you guys.
            >
            > I usually faced this problem in sql 2000.
            >
            >
            > Thanks in advance
            >
            > Cheers,
            > Deepak
            >
            >
            >
            > [Non-text portions of this message have been removed]
            >

            Send instant messages to your online friends http://uk.messenger.yahoo.com

            [Non-text portions of this message have been removed]

            [Non-text portions of this message have been removed]







            [Non-text portions of this message have been removed]
          • Deepak Garg
            Thanks Kuldeep for your reply. Below are given the information you asked for: Yes the job used to hang regularly, while i tried the backup from EM it woked in
            Message 5 of 9 , Jun 5, 2008
            • 0 Attachment
              Thanks Kuldeep for your reply.

              Below are given the information you asked for:

              Yes the job used to hang regularly, while i tried the backup from EM it woked in apprx 2 hrs.

              1.) The server is having SQL 2000 Enterprise Edition with SP3.

              2.) Product Version - 8.00.760

              3.) I am using xp_sqlmaint in backup job step to take database backup.

              Please let me know if anyother information required.


              Regards,
              Deepak

              Kuldeep Chauhan <mrkuldeepchauhan@...> wrote:
              If your job is hanging quite often then of course there would be some genuine problem. Can you please share the information listed below

              1. Which SQL Server version you are using? (Enterprise, Standard etc.). I believe you are running SQL Server 2000.

              2. What is the current service pack applied on box? (SP3, SP4 etc.). Plus provide the Product Version, for example 8.00.3161 etc.

              3. If possible, share what steps your job is performing? is it only backup or some other maintenance tasks along with it.

              Thanks,
              Kuldeep

              Deepak Garg <deepakgrg@...> wrote:
              Thanks Sunil and Madhu for your response on it.

              Adding to my below problem. The job used to hang every day and takes no of days (i notices over weekend) to killed/rollback. yesterday i ve restarted sql service to flush it and taken manually backup from EM it has taken approx 1 and half hour to complete(size of DB is approx 74GB. The job again started with its scheduled time in the late evening and was running on its database backup step till in the morning (means again got hang - so i killed and again :( )

              Please note:

              1) I am using xp_sqlmaint in my job while other system database jobs are using it and getting success everyday on the same server.

              2) The drive has enough space for the backup (so no space issue)

              I don’t know if anything else can be checked at my level,

              Please suggest if anyone have any clue or suggestions about it.

              Thanks in advance.

              Regards,
              Deepak

              sunil john <suniljk7@...> wrote:

              Dear Deepak,

              we also faced the same issue on sql 2000 and solved by restarting sql agent.

              Best Regards

              Sunil John

              ----- Original Message ----
              From: Madhu K Nair <madhuotp@...>
              To: sqlcon@yahoogroups.com
              Sent: Monday, June 2, 2008 7:13:20
              Subject: MUGH-SQLCON Re: : forcefully kill the sql job

              If you kill a process (from Management studio or using t-sql KILL )
              system has to rollback the transaction to get the database into a
              consistant stage. There is no other way to do this, you have to wait
              till the process is rollbacked.

              Madhu

              --- In sqlcon@yahoogroups. com, Deepak Garg <deepakgrg@. ..> wrote:
              >
              > Hi All,
              >
              > Is it possible to kill the SQL Agent job forcefully. Because on
              production server its not possible to restart the sql serverice. Some
              of sql jobs used to hand during the different steps, on intirgity
              check, full backup steps etc.
              >
              >
              > I used to kill them from process info but it takes no of days
              sometime to roll back and i didnt find any other option except to
              restart the service of sql. so looking for some idea from you guys.
              >
              > I usually faced this problem in sql 2000.
              >
              >
              > Thanks in advance
              >
              > Cheers,
              > Deepak
              >
              >
              >
              > [Non-text portions of this message have been removed]
              >

              Send instant messages to your online friends http://uk.messenger.yahoo.com

              [Non-text portions of this message have been removed]

              [Non-text portions of this message have been removed]

              [Non-text portions of this message have been removed]







              [Non-text portions of this message have been removed]
            • Kuldeep Chauhan
              Few quick suggestions.... 1. Apply latest service pack of SQL Server 2000 on your box. There are certain bugs / issues that are fixed every time a SP is
              Message 6 of 9 , Jun 5, 2008
              • 0 Attachment
                Few quick suggestions....

                1. Apply latest service pack of SQL Server 2000 on your box. There are certain bugs / issues that are fixed every time a SP is released, so it is always good practice to keep your system up to date overcomes any issues.

                2. See if SQLMAINT.RLL is present in your install location binn\resources\1033. If not, copy this file from any other install location and see your job behaviour.

                3. Looks at KB http://support.microsoft.com/kb/326485/en-us and see if any symptoms match with your case.

                Irrespective to the suggestions provided above, instead of maintenance plan job did you try to create a database backup job manually? If not then try it and see if it works.

                Thanks,
                Kuldeep


                Deepak Garg <deepakgrg@...> wrote:

                Thanks Kuldeep for your reply.

                Below are given the information you asked for:

                Yes the job used to hang regularly, while i tried the backup from EM it woked in apprx 2 hrs.

                1.) The server is having SQL 2000 Enterprise Edition with SP3.

                2.) Product Version - 8.00.760

                3.) I am using xp_sqlmaint in backup job step to take database backup.

                Please let me know if anyother information required.


                Regards,
                Deepak

                Kuldeep Chauhan <mrkuldeepchauhan@...> wrote:
                If your job is hanging quite often then of course there would be some genuine problem. Can you please share the information listed below

                1. Which SQL Server version you are using? (Enterprise, Standard etc.). I believe you are running SQL Server 2000.

                2. What is the current service pack applied on box? (SP3, SP4 etc.). Plus provide the Product Version, for example 8.00.3161 etc.

                3. If possible, share what steps your job is performing? is it only backup or some other maintenance tasks along with it.

                Thanks,
                Kuldeep

                Deepak Garg <deepakgrg@...> wrote:
                Thanks Sunil and Madhu for your response on it.

                Adding to my below problem. The job used to hang every day and takes no of days (i notices over weekend) to killed/rollback. yesterday i ve restarted sql service to flush it and taken manually backup from EM it has taken approx 1 and half hour to complete(size of DB is approx 74GB. The job again started with its scheduled time in the late evening and was running on its database backup step till in the morning (means again got hang - so i killed and again :( )

                Please note:

                1) I am using xp_sqlmaint in my job while other system database jobs are using it and getting success everyday on the same server.

                2) The drive has enough space for the backup (so no space issue)

                I don’t know if anything else can be checked at my level,

                Please suggest if anyone have any clue or suggestions about it.

                Thanks in advance.

                Regards,
                Deepak

                sunil john <suniljk7@...> wrote:

                Dear Deepak,

                we also faced the same issue on sql 2000 and solved by restarting sql agent.

                Best Regards

                Sunil John

                ----- Original Message ----
                From: Madhu K Nair <madhuotp@...>
                To: sqlcon@yahoogroups.com
                Sent: Monday, June 2, 2008 7:13:20
                Subject: MUGH-SQLCON Re: : forcefully kill the sql job

                If you kill a process (from Management studio or using t-sql KILL )
                system has to rollback the transaction to get the database into a
                consistant stage. There is no other way to do this, you have to wait
                till the process is rollbacked.

                Madhu

                --- In sqlcon@yahoogroups. com, Deepak Garg <deepakgrg@. ..> wrote:
                >
                > Hi All,
                >
                > Is it possible to kill the SQL Agent job forcefully. Because on
                production server its not possible to restart the sql serverice. Some
                of sql jobs used to hand during the different steps, on intirgity
                check, full backup steps etc.
                >
                >
                > I used to kill them from process info but it takes no of days
                sometime to roll back and i didnt find any other option except to
                restart the service of sql. so looking for some idea from you guys.
                >
                > I usually faced this problem in sql 2000.
                >
                >
                > Thanks in advance
                >
                > Cheers,
                > Deepak
                >
                >
                >
                > [Non-text portions of this message have been removed]
                >

                Send instant messages to your online friends http://uk.messenger.yahoo.com

                [Non-text portions of this message have been removed]

                [Non-text portions of this message have been removed]

                [Non-text portions of this message have been removed]

                [Non-text portions of this message have been removed]







                [Non-text portions of this message have been removed]
              • Deepak
                Kuldeep, I am totally convinced with your first point, we must always be at the heighest service pack, these servers are goona migrate with 2005 and there are
                Message 7 of 9 , Jun 5, 2008
                • 0 Attachment
                  Kuldeep,

                  I am totally convinced with your first point, we must always be at the heighest service pack, these servers are goona migrate with 2005 and there are few other servers which are running with same configuration and with same SP3, no there is no reson which i justify 'Business' to upgrade them to SP4 and im not sure its goona solve this problem.

                  2.) I have checked for SQLMAINT.RLL file its available in the server under C:\Program Files\Microsoft SQL Server\MSSQL$ABCD2\Binn\Resources\1033 and its size is 32KB

                  3.) The article seems useful but im not getting any error because the job never failed it got hanged everytime. And the article suggested "do not use strings that contain "-S" for a directory path" Here i am using "D:\DATA\MSSQL$ABXXX\BACKUP\XXX" . I dont think $ would be creating a problem as system dbs backup are using the same directory.

                  Yes i can try to create a database backup job manually, but the last job killed (2 days back) is still in killed/rollback process while check the status everytime it shows SPID 64: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

                  Without its flushing even im not able to manually backed up my production database and its on high risk :(

                  Cheers,
                  Deepak

                  Kuldeep Chauhan <mrkuldeepchauhan@...> wrote:

                  Few quick suggestions....

                  1. Apply latest service pack of SQL Server 2000 on your box. There are certain bugs / issues that are fixed every time a SP is released, so it is always good practice to keep your system up to date overcomes any issues.

                  2. See if SQLMAINT.RLL is present in your install location binn\resources\1033. If not, copy this file from any other install location and see your job behaviour.

                  3. Looks at KB http://support.microsoft.com/kb/326485/en-us and see if any symptoms match with your case.

                  Irrespective to the suggestions provided above, instead of maintenance plan job did you try to create a database backup job manually? If not then try it and see if it works.

                  Thanks,
                  Kuldeep


                  Deepak Garg <deepakgrg@...> wrote:

                  Thanks Kuldeep for your reply.

                  Below are given the information you asked for:

                  Yes the job used to hang regularly, while i tried the backup from EM it woked in apprx 2 hrs.

                  1.) The server is having SQL 2000 Enterprise Edition with SP3.

                  2.) Product Version - 8.00.760

                  3.) I am using xp_sqlmaint in backup job step to take database backup.

                  Please let me know if anyother information required.

                  Regards,
                  Deepak

                  Kuldeep Chauhan <mrkuldeepchauhan@...> wrote:
                  If your job is hanging quite often then of course there would be some genuine problem. Can you please share the information listed below

                  1. Which SQL Server version you are using? (Enterprise, Standard etc.). I believe you are running SQL Server 2000.

                  2. What is the current service pack applied on box? (SP3, SP4 etc.). Plus provide the Product Version, for example 8.00.3161 etc.

                  3. If possible, share what steps your job is performing? is it only backup or some other maintenance tasks along with it.

                  Thanks,
                  Kuldeep

                  Deepak Garg <deepakgrg@...> wrote:
                  Thanks Sunil and Madhu for your response on it.

                  Adding to my below problem. The job used to hang every day and takes no of days (i notices over weekend) to killed/rollback. yesterday i ve restarted sql service to flush it and taken manually backup from EM it has taken approx 1 and half hour to complete(size of DB is approx 74GB. The job again started with its scheduled time in the late evening and was running on its database backup step till in the morning (means again got hang - so i killed and again :( )

                  Please note:

                  1) I am using xp_sqlmaint in my job while other system database jobs are using it and getting success everyday on the same server.

                  2) The drive has enough space for the backup (so no space issue)

                  I don’t know if anything else can be checked at my level,

                  Please suggest if anyone have any clue or suggestions about it.

                  Thanks in advance.

                  Regards,
                  Deepak

                  sunil john <suniljk7@...> wrote:

                  Dear Deepak,

                  we also faced the same issue on sql 2000 and solved by restarting sql agent.

                  Best Regards

                  Sunil John

                  ----- Original Message ----
                  From: Madhu K Nair <madhuotp@...>
                  To: sqlcon@yahoogroups.com
                  Sent: Monday, June 2, 2008 7:13:20
                  Subject: MUGH-SQLCON Re: : forcefully kill the sql job

                  If you kill a process (from Management studio or using t-sql KILL )
                  system has to rollback the transaction to get the database into a
                  consistant stage. There is no other way to do this, you have to wait
                  till the process is rollbacked.

                  Madhu

                  --- In sqlcon@yahoogroups. com, Deepak Garg <deepakgrg@. ..> wrote:
                  >
                  > Hi All,
                  >
                  > Is it possible to kill the SQL Agent job forcefully. Because on
                  production server its not possible to restart the sql serverice. Some
                  of sql jobs used to hand during the different steps, on intirgity
                  check, full backup steps etc.
                  >
                  >
                  > I used to kill them from process info but it takes no of days
                  sometime to roll back and i didnt find any other option except to
                  restart the service of sql. so looking for some idea from you guys.
                  >
                  > I usually faced this problem in sql 2000.
                  >
                  >
                  > Thanks in advance
                  >
                  > Cheers,
                  > Deepak
                  >
                  >
                  >
                  > [Non-text portions of this message have been removed]
                  >

                  Send instant messages to your online friends http://uk.messenger.yahoo.com

                  [Non-text portions of this message have been removed]

                  [Non-text portions of this message have been removed]

                  [Non-text portions of this message have been removed]

                  [Non-text portions of this message have been removed]

                  [Non-text portions of this message have been removed]







                  [Non-text portions of this message have been removed]
                • Gigme Chittayath
                  Sometimes, after creating maintenance plan in sql 2000, if we modify the plan, it may not like it.Such cases i would try to recreate the maintenance plan.If
                  Message 8 of 9 , Jun 5, 2008
                  • 0 Attachment
                    Sometimes, after creating maintenance plan in sql 2000, if we modify the plan, it may not like it.Such cases i would try to recreate the maintenance plan.If you are running integrity
                    job everyday, then it may take some time.Try to create only the full back up & transaction log in one maintenance plan & others in another.
                    Also you could try to create a backup job with out using maintenance plan .
                    Its the problem with maintenance plan ,so backup script should be working.
                    Also in maintenance plan , integrity check, 'attempt to repair minor problems' if we uncheck it ,it used to work in some cases.
                    Gigme.

                    Deepak <deepakgrg@...> wrote:
                    Kuldeep,

                    I am totally convinced with your first point, we must always be at the heighest service pack, these servers are goona migrate with 2005 and there are few other servers which are running with same configuration and with same SP3, no there is no reson which i justify 'Business' to upgrade them to SP4 and im not sure its goona solve this problem.

                    2.) I have checked for SQLMAINT.RLL file its available in the server under C:\Program Files\Microsoft SQL Server\MSSQL$ABCD2\Binn\Resources\1033 and its size is 32KB

                    3.) The article seems useful but im not getting any error because the job never failed it got hanged everytime. And the article suggested "do not use strings that contain "-S" for a directory path" Here i am using "D:\DATA\MSSQL$ABXXX\BACKUP\XXX" . I dont think $ would be creating a problem as system dbs backup are using the same directory.

                    Yes i can try to create a database backup job manually, but the last job killed (2 days back) is still in killed/rollback process while check the status everytime it shows SPID 64: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

                    Without its flushing even im not able to manually backed up my production database and its on high risk :(

                    Cheers,
                    Deepak

                    Kuldeep Chauhan <mrkuldeepchauhan@...> wrote:

                    Few quick suggestions....

                    1. Apply latest service pack of SQL Server 2000 on your box. There are certain bugs / issues that are fixed every time a SP is released, so it is always good practice to keep your system up to date overcomes any issues.

                    2. See if SQLMAINT.RLL is present in your install location binn\resources\1033. If not, copy this file from any other install location and see your job behaviour.

                    3. Looks at KB http://support.microsoft.com/kb/326485/en-us and see if any symptoms match with your case.

                    Irrespective to the suggestions provided above, instead of maintenance plan job did you try to create a database backup job manually? If not then try it and see if it works.

                    Thanks,
                    Kuldeep

                    Deepak Garg <deepakgrg@...> wrote:

                    Thanks Kuldeep for your reply.

                    Below are given the information you asked for:

                    Yes the job used to hang regularly, while i tried the backup from EM it woked in apprx 2 hrs.

                    1.) The server is having SQL 2000 Enterprise Edition with SP3.

                    2.) Product Version - 8.00.760

                    3.) I am using xp_sqlmaint in backup job step to take database backup.

                    Please let me know if anyother information required.

                    Regards,
                    Deepak

                    Kuldeep Chauhan <mrkuldeepchauhan@...> wrote:
                    If your job is hanging quite often then of course there would be some genuine problem. Can you please share the information listed below

                    1. Which SQL Server version you are using? (Enterprise, Standard etc.). I believe you are running SQL Server 2000.

                    2. What is the current service pack applied on box? (SP3, SP4 etc.). Plus provide the Product Version, for example 8.00.3161 etc.

                    3. If possible, share what steps your job is performing? is it only backup or some other maintenance tasks along with it.

                    Thanks,
                    Kuldeep

                    Deepak Garg <deepakgrg@...> wrote:
                    Thanks Sunil and Madhu for your response on it.

                    Adding to my below problem. The job used to hang every day and takes no of days (i notices over weekend) to killed/rollback. yesterday i ve restarted sql service to flush it and taken manually backup from EM it has taken approx 1 and half hour to complete(size of DB is approx 74GB. The job again started with its scheduled time in the late evening and was running on its database backup step till in the morning (means again got hang - so i killed and again :( )

                    Please note:

                    1) I am using xp_sqlmaint in my job while other system database jobs are using it and getting success everyday on the same server.

                    2) The drive has enough space for the backup (so no space issue)

                    I don’t know if anything else can be checked at my level,

                    Please suggest if anyone have any clue or suggestions about it.

                    Thanks in advance.

                    Regards,
                    Deepak

                    sunil john <suniljk7@...> wrote:

                    Dear Deepak,

                    we also faced the same issue on sql 2000 and solved by restarting sql agent.

                    Best Regards

                    Sunil John

                    ----- Original Message ----
                    From: Madhu K Nair <madhuotp@...>
                    To: sqlcon@yahoogroups.com
                    Sent: Monday, June 2, 2008 7:13:20
                    Subject: MUGH-SQLCON Re: : forcefully kill the sql job

                    If you kill a process (from Management studio or using t-sql KILL )
                    system has to rollback the transaction to get the database into a
                    consistant stage. There is no other way to do this, you have to wait
                    till the process is rollbacked.

                    Madhu

                    --- In sqlcon@yahoogroups. com, Deepak Garg <deepakgrg@. ..> wrote:
                    >
                    > Hi All,
                    >
                    > Is it possible to kill the SQL Agent job forcefully. Because on
                    production server its not possible to restart the sql serverice. Some
                    of sql jobs used to hand during the different steps, on intirgity
                    check, full backup steps etc.
                    >
                    >
                    > I used to kill them from process info but it takes no of days
                    sometime to roll back and i didnt find any other option except to
                    restart the service of sql. so looking for some idea from you guys.
                    >
                    > I usually faced this problem in sql 2000.
                    >
                    >
                    > Thanks in advance
                    >
                    > Cheers,
                    > Deepak
                    >
                    >
                    >
                    > [Non-text portions of this message have been removed]
                    >

                    Send instant messages to your online friends http://uk.messenger.yahoo.com

                    [Non-text portions of this message have been removed]

                    [Non-text portions of this message have been removed]

                    [Non-text portions of this message have been removed]

                    [Non-text portions of this message have been removed]

                    [Non-text portions of this message have been removed]

                    [Non-text portions of this message have been removed]







                    [Non-text portions of this message have been removed]
                  Your message has been successfully submitted and would be delivered to recipients shortly.