Windows Server 2008R2, MSSQL server 2008, 32 GB RAM setup as a VM on xenserver 6.2
Unfortunately, I am not a Database administrator by any stretch, but am being thrust into that role with some issues we are having.
Using SQL Server Manager Studio we had a maintenance plan set up to make a full database backup every 6 hours
Database backups had expanded to the 37 GB range
About every couple of days, the plan would run, but it would not generate the maintenance plan text file that usually goes with the backup file.
When that happened, when we wanted to move that file off the the backup drive to storage, it was locked "by another process"
The next scheduled maintenance would not run, nor would manual backups when launched.
Rebooting the server is the only way we have found to unlock the file, and then it would be fine for a while.
Have since shut off the scheduled backup and have been doing manual backups to see if we could get more information from the system about what is happening. One did fail last night and we got event 3041 "Backup failed .... check backup log for detailed messages."
Checking the logs by going to Management > SQL Server Logs I do not see anything too helpful during that time except an entry that says "Error 3041, Severity: 16, State: 1." at essentially the same time as the 3041 in the event viewer ("Backup failed .... check backup log for detailed messages.")
We also do get a few Error 833's ("I/O requests taking longer than 15 seconds ...") during the time the full backups are running.
We have been also figuring out and researching the differential backup options, but could use some pointers as we are not sure of the mechanics of that.
The big question: Why are the backups failing?
There must be more information somewhere in logs I am missing?
What is the best way to improve this situation?
What are the proper steps to have a successful full / differential backup / recovery strategy?
I am unclear how transaction log backups fit into this. Pointers?