SQL Server Performance

users and database backup

Discussion in 'Performance Tuning for DBAs' started by itfbd, Sep 20, 2007.

Page 1 of 2

  1. itfbd New Member

    Is it necessary that we should disconnect users from the database while taking it's backup ? Why ?
    itfbd, Sep 20, 2007
    #2

  2. martins New Member

    It isn't necessary, but users might experience a drop in performance and locking/blocking might occur...especially if your users are trying to write data to the database.
    It is always good practise to do backups during low traffic, and possibly (if a full backup) when no users are connected.
    martins, Sep 21, 2007
    #3

  3. Jack Vamvas Member

    You could also consider timing the backup while other Anti Virus is not running , other jobs aren't running to minimise bottlenecks
    Jack Vamvas, Sep 21, 2007
    #4

  4. ghemant Moderator

    That's why it is recommended to schedule database backup in off-pick hour.
    ghemant, Sep 21, 2007
    #5

  5. itfbd New Member

    I was asking because we are facing a big issue while taking sql database backup. It takes 1 hour to backup a 13 GB database and effects the users.
    no idea whether there is any fault in our server or database ...
    We have a Xeon 2.8 dual processor with 4 GB RAM and RAID 5 configured HCL server.
    Kindly advice.
    itfbd, Sep 21, 2007
    #6

  6. ghemant Moderator

    Is any Antivirus running on your server? If so ensure database files are excluded, also check for disk I/O...
    How do you take backup? Do you take backup using third party tool or native backup? Is their any other activity schedule that access this database during time of backup?
    ghemant, Sep 24, 2007
    #7

  7. itfbd New Member

    Yes Anti Virus is running on the server but i tried disabling it during the backup but no use.
    We have a backup script that is executed as a SQL scheduled job thrice a day.
    No other job is running at that time.
    Can i try reinstalling the SQL Server and database attach - deattach, if no other option available ?
    HELP Please!!! It's Killing!!!
    itfbd, Sep 25, 2007
    #8

  8. satya Moderator

    You may have to perform the hardware chekcup as it should not block or have slow performance affect while performing backup, if the disks are not configured properly for WRITE then you will get such issues.
    Also what is the schedule of backups?
    satya, Sep 25, 2007
    #9

  9. itfbd New Member

    The Backup Schedule is 6 AM, 2PM, 10 PM daily.
    itfbd, Sep 25, 2007
    #10

  10. satya Moderator

    Is this schedule for full database backup?
    If so why not you schedule 1 hour transaction log backup and only perform FULL DATABASE BACKUP overnight.
    satya, Sep 25, 2007
    #11

  11. itfbd New Member

    This is the script we are using for backup..
    BACKUP DATABASE Ldbase to Disk='F:DatabaseDailyBackupLdbase.dmp' with INIT, NOUNLOAD , NAME = N'Ldbase backup', NOSKIP , STATS = 10, NOFORMAT
    itfbd, Sep 25, 2007
    #12

  12. satya Moderator

    It seems you are performing only FULL database backups, as suggested you should takeup transaction log backups every 1hour and perform full db bacup overnight in order to reduce this contention.
    satya, Sep 25, 2007
    #13

  13. itfbd New Member

    Since we are taking backup thrice a day, we are disconnecting the database from users during backup.
    Then if we take transaction backup every 1 hour, how to disconnect ...?
    Can u type the script as how to do so ?
    itfbd, Sep 25, 2007
    #14

  14. satya Moderator

    satya, Sep 25, 2007
    #15

  15. itfbd New Member

    This was my first question that should i disconnect the users while taking the backup. As i thought that it may further slow down the backup process.
    Though I know the difference between Full , differential and log backup i thought that Full database backup is more efficient and safe.
    itfbd, Sep 25, 2007
    #16

  16. satya Moderator

    I believe you are circling around one issue and ignoring other causes for this issue.
    As you say the full database backup is causing issues, as I have suggested you have to check the hardware too where it will slow down other issues if the disks are not configured properly.
    Also as suggested go through transaction log backups every hour and overnight backup for better managability due to the current issues you have.
    satya, Sep 25, 2007
    #17

  17. itfbd New Member

    Ok, Got the point . I'll ask for the server vendor for hardware part.
    Is it ok if i take differential backup twice a day and full backup once at night in the same dir overwriting the dump file.
    OR should i take these backups in different folders maintaing two diff dump files (one full and one diff) ?
    itfbd, Sep 25, 2007
    #18

  18. satya Moderator

    If you overwrite the file how you will be able recover from the last bacup, if the point in time recovery is not an option then you can go with 3 differential during day and 1 full database backups overnight.
    satya, Sep 25, 2007
    #19

  19. itfbd New Member

    I'll manage 2 different files (one full and other for diff) and also look after the hardware issue.
    Really Thanks for the support and quick replies.
    itfbd, Sep 25, 2007
    #20
Page 1 of 2

Share This Page