Discussion in 'Performance Tuning for DBAs' started by itfbd, Sep 20, 2007.
Is it necessary that we should disconnect users from the database while taking it's backup ? Why ?
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.
You could also consider timing the backup while other Anti Virus is not running , other jobs aren't running to minimise bottlenecks
That's why it is recommended to schedule database backup in off-pick hour.
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.
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?
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!!!
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?
The Backup Schedule is 6 AM, 2PM, 10 PM daily.
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.
This is the script we are using for backup..
BACKUP DATABASE Ldbase to Disk='FatabaseDailyBackupLdbase.dmp' with INIT, NOUNLOAD , NAME = N'Ldbase backup', NOSKIP , STATS = 10, NOFORMAT
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.
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 ?
No need to disconnect, who suggested that?
Within SQL Server you can perform online backup without interrupting the users.
Refer to books online about basics on backups and transaction log in thsi case.
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.
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.
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) ?
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.
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.
Separate names with a comma.