Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    3

    Unanswered: DB Backup using Scheduled Jobs

    I have a database that runs Stored Procedures scheduled as jobs throughout the night to keep data current. I also have a job that backs up the database and does Integrity checks at night. This job often fails due to not being in single user mode.

    Is there a way I can schedule this Maintanance using Jobs still and get around the Single User Mode problem. I want this to run while I am not around so it needs to be fully automated.

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: DB Backup using Scheduled Jobs

    RE:
    Q1 Is there a way I can schedule this Maintenance using Jobs still and get around the Single User Mode problem.
    A1 Yes (depending on exactly what is meant by "get around the Single User Mode problem"). For example:

    a One could re-schedule the jobs (such that no conflicts will occur).

    b One could simply avoid using repair options with any of the DBCC statements in scheduled maintenance jobs i.e.(Repair_Allow_Data_Loss, Repair_Fast, Repair_Rebuild); and manually re-execute the applicable Dbcc statements with the repair option(s) when necessary. (If the maintenance jobs are maintenance wizard generated, uncheck the repair / fix errors check box in the wizard interface.)

    RE:
    Q2 I want this to run while I am not around so it needs to be fully automated.
    A2 To 'fully automate' one could write jobs and / or stored procedures that examine the results of Dbcc Checkdb (run without repair options) and then conditionally set the db into single user mode; and then run Dbcc CheckDB, or Dbcc Checktable and / or Dbcc Checkalloc with the appropriate repair option(s), only when such repairs are necessary.

  3. #3
    Join Date
    Aug 2002
    Posts
    3
    DBA,

    At the bottom of your response, you said:

    "examine the results of Dbcc Checkdb (run without repair options) and then conditionally set the db into single user mode...when necessary"

    So there is T-SQL that can be written which will allow me to switch in and out of Single User mode without me manually doing it?


    Also you said:

    "re-schedule the jobs (such that no conflicts will occur)."

    What actions result in a conflict? No actual job is running at the exact time these are failing. I do see one SPID for this particular database with a status of sleeping. Regardless of how many times I try to kill this process nothing changes.


    Finally if I uncheck the auto-repair feature in the maintanance wizard how will I know when there is an issue. Will I have to regularly view the maintanance logs. Will it give me any type of visible notification?

  4. #4
    Join Date
    Oct 2002
    Posts
    369
    (just have a few minutes will add more later; but for now here is a partial answer...)

    RE:
    Q3 So there is T-SQL that can be written which will allow me to switch in and out of Single User mode without me manually doing it?
    A3 Yes, with current versions there are actually two choices:
    1 sp_DBOption
    2 Alter DataBase

    RE:
    "re-schedule the jobs (such that no conflicts will occur)."
    Q4 What actions result in a conflict?
    A4 In the case of setting the DB to single user mode to be repaired via a repair clause in any of the applicable Dbcc options, another connection of any kind performing any action, or no action at all.

  5. #5
    Join Date
    Oct 2002
    Posts
    369
    RE:

    Q5 Finally if I uncheck the auto-repair feature in the maintanance wizard how will I know when there is an issue. Will I have to regularly view the maintanance logs. Will it give me any type of visible notification?
    Q5 Finally if I uncheck the auto-repair feature in the maintenance wizard how will I know when there is an issue. Will I have to regularly view the maintenance logs. Will it give me any type of visible notification?

    A5 i
    Returned dbcc result sets include error information (even With No_InfoMsgs). A job step with a dbcc checkdb result that reports allocation or consistency errors will normally be marked as 'failed', {the same should therefore also normally be expected to hold true for maintenance wizard generated jobs}. Some example dbcc CheckDB errors:
    Server: Msg 7965, Level 16, State 1, Line 1
    Table error: Could not check object ID 1093578934, index ID 2 due to invalid allocation page(s).
    There are 23 rows in 1 pages for object 'Authorities'.
    CHECKDB found 3 allocation errors and 2 consistency errors in table 'Authorities' (object ID 1093578934).
    CHECKDB found 3 allocation errors and 2 consistency errors in database 'Demo'.

    A5 ii
    To fix such dbcc errors found, one might use something like the following example (as appropriate for the business needs and the environment) in a job that is fired off following the failure of a dbcc CheckDB (job / job step, etc.,) run without a repair clause:

    Use NorthWind
    --
    Alter DataBase NorthWind
    Set
    RESTRICTED_USER
    With RollBack After 60 --> RollBack after this many seconds

    Alter DataBase
    NorthWind
    Set
    SINGLE_USER
    With
    RollBack Immediate --> Do it NOW

    dbcc CheckDB ('NorthWind', Repair_Allow_Data_Loss) With All_ErrorMsgs

    -- Other Alter DataBase Set (MODE) With options:
    -- No With clause <-- wait until current trans are done, (until not busy)
    -- With No_Wait <-- If trans in progress, (busy), forget it (don't Alter)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •