Post reply

SQL Server agent job was not running last night.

  • June 17, 2015 at 1:36 am

    #299063

    Hi,

    I am using SQL Server 2008 R2. I am using one job to transfer the data from one table to another table by using stored procedure. Now the job is running successfully. But yesterday mid night the job has stopped with the error log is like "Database is in readonly mode". But this is not happened frequently.

    I cannot understand why the job has stopped suddenly. And how to identify the issue which stop the job?

    Any one please help me out....

    Manik
    You cannot get to the top by sitting on your bottom.

  • June 17, 2015 at 2:38 am

    #1805964

    Something turned your database to readonly. Looking at the ERRORLOG you shoud see an entry similar to this before the job ran:

    Setting database option READ_ONLY to ON for database 'Yourdatabase'.

    If you're lucky enough, you should also find an entry in the default trace with more detail:

    DECLARE @FileName VARCHAR(MAX)

    SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'

    FROM sys.traces

    WHERE is_default = 1;

    SELECT

    gt.EventClass,

    e.name as EventName,

    gt.TextData,

    gt.ObjectID,

    gt.ObjectName,

    gt.DatabaseName,

    gt.SessionLoginName,

    gt.StartTime,

    gt.ApplicationName,

    gt.HostName,

    gt.NTUserName,

    gt.NTDomainName

    FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt

    JOIN sys.trace_events e

    ON gt.EventClass = e.trace_event_id

    WHERE gt.EventClass = 164 -- Object Altered Event

    AND ObjectType = 16964 -- Database Object

    -- Gianluca Sartori

  • June 17, 2015 at 3:17 am

    #1805972

    Thanks Gianluca Sartori... How can i find out the issue by using the above query? any column looks strange when that error raise?

    Manik
    You cannot get to the top by sitting on your bottom.

  • June 17, 2015 at 3:28 am

    #1805978

    If the query returns rows, it means that something has changed the database status.

    Each row has user name and time when the change happened, which should help you track down the source.

    -- Gianluca Sartori

  • June 17, 2015 at 4:17 am

    #1805996

    Thanks Gianluca Sartori...

    Manik
    You cannot get to the top by sitting on your bottom.

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

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