Professional Web Applications Themes

Backing up with a low priority - Microsoft SQL / MS SQL Server

I perform a series of steps via the SQL Agent to backup a database to a file and find that as the database grows it now consumes so much processor time that clients recieve timeout errors. The database must be available 24/7 and so is there a way to run the steps with a low priority therefore giving priority to client requests? DBCC CHECKDB ('DBNAME') With NO_INFOMSGS, TABLERESULTS DBCC SHRINKDATABASE (DBNAME , 10) BACKUP DATABASE DBNAME to DBNAMEFile WITH INIT MJ...

  1. #1

    Default Backing up with a low priority

    I perform a series of steps via the SQL Agent to backup a database to a file
    and find that as the database grows it now consumes so much processor time
    that clients recieve timeout errors. The database must be available 24/7 and
    so is there a way to run the steps with a low priority therefore giving
    priority to client requests?

    DBCC CHECKDB ('DBNAME') With NO_INFOMSGS, TABLERESULTS
    DBCC SHRINKDATABASE (DBNAME , 10)
    BACKUP DATABASE DBNAME to DBNAMEFile WITH INIT

    MJ


    MJ Guest

  2. #2

    Default Re: Backing up with a low priority

    I dont think that such priorities can be set at the SQL Server level ...

    --
    HTH,
    Vinod Kumar
    MCSE, DBA, MCAD
    SCT Software Solutions
    http://www32.brinkster.com/sqlvinod


    "MJ" <co.uk> wrote in message news:bfb108$e7b$1$demon.co.uk... 
    Vinodk Guest

  3. #3

    Default Re: Backing up with a low priority

    I agree 100%. Don't shrink the db unless absolutely necessary. The db
    needs lots of free space just to carry out normal activities such as
    reindexing, growing , page splits etc.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Mike John" <com> wrote in message
    news:phx.gbl...
    Thre is no way I am aware of to lower backup priority, but I would check
    which step is causing the problem. I expect the SHRINK is the issue rather
    than the backup.

    I very rarely shrink production databases, and to do so regularly as part of
    a backup is rather unusual. Do you really need the shrink, after all if you
    are doing it a lot it implies the db is growing again, maybe it i sbetter
    left 'unshrunk'

    Mike John


    "MJ" <co.uk> wrote in message
    news:bfb108$e7b$1$demon.co.uk... 
    file 
    and 


    Andrew Guest

Similar Threads

  1. Priority Caching
    By mrgfunk in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: February 16th, 06:00 PM
  2. loading with priority
    By Phatty in forum Macromedia Flash
    Replies: 0
    Last Post: June 16th, 12:46 AM
  3. how to give priority to a nic
    By Robert L [MS-MVP] in forum Windows Networking
    Replies: 0
    Last Post: July 16th, 01:52 PM
  4. Put in priority order
    By Marta in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 15th, 04:19 PM
  5. TCP-IP high priority ove IPX
    By mara in forum Windows Networking
    Replies: 1
    Last Post: July 2nd, 06:17 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139