July 25, 2006 at 7:00 am
For a while now, my company has been working to migrate from a VAX system to SQL Server 7. But until this task is complete, it is necessary to DTS data on a daily basis from the VAX. Because of this, and the large volume of data moved on a daily basis, our SQL databases were created to allow Select Into/Bulk Copy.
Because of this, we consistently have problems with transaction log backups failing (non-logged trans). I thought a solution would be to execute full backups daily, and differentials throughout the day. It appeared to be working, as the differentials were completing in about 10 minutes (scheduled for every 30 minutes). However, after 2 successful differentials, the job ran for an hour before I cancelled it. A co-worker later came to me to help determine why one of his jobs was failing. His job was one that DTS'd data from the VAX to SQL. It appears that while the differential backup did not cause any performance issues with users (no calls to helpdesk), there is a conflict with DTS using bulk copy. My guess is that his job was causing my backup to run long, and my backup was causing his job to fail. Aside from turning off select into/bulk copy, I am looking for a suggestion on how to backup our databases throughout the day. Any help would be greatly appreciated.
July 25, 2006 at 7:32 am
Instead of taking differentials every 30 min. Think of Backup schedule, where you take one Full Backup , one or two [6/8 hrs. apart] differential and transaction logs every 30 min. Make sure your differentials do not run the same time your coworkers DTS job run. Tran. log backup will definitely be fast operation and you will be able to avoid blocking and can get better performance.
Hope this helps,
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply