Post reply

Job Started Out Of/Without Schedule, Sql Server 2000

  • January 27, 2006 at 9:30 am

    #93364

    Last night I have seen my Database Backup Job runs twice, once as per schedule

    At 12.30 AM and then again out of schedule at 1 AM.

     

    Job is scheduled to run twice a day: Occurs every 12 hour at 12.30 am & 12.30 pm

    I have checked EM as well as sysjobschedules. Further this job runs for more than 1 year without any issues. I am using sql server 2000-SP3 on Windows-NT-5.

     

    Here is description of 3 Steps in JOB

    1. Backup Log  <Filename> With Truncate_Only

        DBCC Shrinkfile(Filename_log, 150)

     

    Go to next step in both cases:  failure and success

     

    2. Backup Database Filename

        To FilenameBackup 

        With Init, Name = ' FilenameBackup '

     

    Go to next step when success

     

    3. Backup Log Filename

        To FilenameLog

         With Init, Name = ' FilenameLog'

     

    Job that runs as per scheduled last night 12.30 AM gives this result

     

    Step 1: Truncate DbName_Log File    Result:  Success

    . Cannot shrink log file 2 (Dbname_Log) because all logical log files are in use. [SQLSTATE 01000] (Message 9008)

     

    Step 2: 'DbName Database Backup     Result: Success

    Processed 252112 pages for database 'Dbname', file 'Dbname_Data' on file 1. [SQLSTATE 01000] (Message 4035)  Processed 212 pages for database 'Dbname', file 'Dbname'_Log' on file 1. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 252324 pages in 119.849 seconds (17.246 MB/sec). [SQLSTATE 01000] (Message 3014).  The step succeeded.

     

    Step 3 : DbName First Log             Result : Success

    Processed 212 pages for database 'Dbname', file 'Dbname'_Log' on file 1. [SQLSTATE 01000] (Message 4035)  BACKUP LOG successfully processed 212 pages in 0.389 seconds (4.454 MB/sec). [SQLSTATE 01000] (Message 3014).  The step succeeded.

     

    Step 0: Success

    Job Outcome Success in 2 minutes

     

    Out of schedule runs again at 1 AM

     

    Then I see same Job running at 1 Am, which is not the schedule time.

     

    Step 1: Truncate DbName_Log File    Result:  Success

    DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528). 

     

    Step 2: 'DbName Database Backup     Result: Success

    Processed 252128 pages for database 'Dbname', file 'Dbname'_Data' on file 1. [SQLSTATE 01000] (Message 4035)  Processed 11 pages for database 'Dbname', file 'Dbname'_Log' on file 1. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 252139 pages in 112.924 seconds (18.291 MB/sec). [SQLSTATE 01000] (Message 3014).  The step succeeded.

     

    Step 3: DbName First Log             Result: Success

    Processed 12 pages for database 'Dbname', file 'Dbname'_Log' on file 1. [SQLSTATE 01000] (Message 4035)  BACKUP LOG successfully processed 12 pages in 0.159 seconds (0.573 MB/sec). [SQLSTATE 01000] (Message 3014).  The step succeeded.

     

    Step 0 : Success

    Job Outcome Success in 2 minutes

     

    Question:

     

    1. What makes job run at 1 Am without  Schedule?
    2. At 12.30 it says, Cannot Shrink Log File2 in first step

    But it creates new log File1 in that same Job step 3.

     

    I have only one log file, so don’t know what’s going on there

    With File2 and File1?

     

    This morning I have checked the hung transaction thru DBCC, that there is no active transaction, and particularly not much activity during last night at all.

     

    I will highly appreciate knowledge-based updates from expert friends here.

     

     

     

     

    Kindest Regards,

    Sameer Raval [Sql Server DBA]
    Geico Insurance
    RavalSameer@hotmail.com

  • January 27, 2006 at 12:51 pm

    #617556

    Sameer,

    In the job schedule, is it configured to have two seperate schedules, one for the job run at 12:30am and one for the job run at 12:30pm or does it run off of one schedule and configured to run "every 12 hours?"

    Mark

  • January 27, 2006 at 12:58 pm

    #617558

    Mark,

    It runs from one schedule that says,

    occurs every one day every 12 hours between 12.30.00 AM and 11.59.59 PM.

    Thanks,

     

     

    Kindest Regards,

    Sameer Raval [Sql Server DBA]
    Geico Insurance
    RavalSameer@hotmail.com

Viewing 3 posts - 1 through 2 (of 2 total)

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