IT
asked on
SBS 2011 - SQL Backup errors after Virtualization
Hi there,
We have recently virtualized one of the sites we look after in Hyper-V and ever since we have done this, the event logs are being filled with SQL related backup errors. Nothing seems to break when these errors occur however I would like to get to the bottom of this.
As there are a number of errors, please download the attached event log extract for the entire string. These errors occur exactly every 4 hours without fail.
https://drive.google.com/f
I have done the following to try and troubleshoot this:
- Checked task scheduler for conflicting schedules, nothing coincides with the timing of these errors
- Checked Backups; Windows backup has been turned off completely as we use Shadowprotect. The Shadowprotect schedule is set to take incremental backups during business hours. These errors happen around the clock.
- Installed SQL server 2008 servicepack 3 and applied all available Windows updates
- Checked VSS Writer health. Intermittently, the majority of writers fail, see attached. Occasionally, all the writers come up as ok. See attached for detail of the writer fails.
- Applied the latest update for our Add2Exchange application that runs on SQL. I also had their support do a once-over on the server to check exchange health etc. Apart from a few changes to permissions to improve security, no problems were found.
- Did some due diligence and ran Google searches on a few of the errors, none of the individual solutions I found seemed to be applicable for this due to the number of different errors and I could find no real info on SQL internal backups as I think the search queries were being skewed by the word backup. I kept getting hits about Windows Backup VSS problems which don't apply here.
- SQL Server 2005 is still installed on this server as the Windows Internal Database seems to run on it. Add2Exchange also seems to rely on this. One post I found suggested I uninstall it but I am not game enough to do this without fully understanding the implications of doing this.
Can you guys please take a look at these logs for me as I am not quiet sure where to go from here.
Much appreciated
VSS-Writer-Health.txt
We have recently virtualized one of the sites we look after in Hyper-V and ever since we have done this, the event logs are being filled with SQL related backup errors. Nothing seems to break when these errors occur however I would like to get to the bottom of this.
As there are a number of errors, please download the attached event log extract for the entire string. These errors occur exactly every 4 hours without fail.
https://drive.google.com/f
I have done the following to try and troubleshoot this:
- Checked task scheduler for conflicting schedules, nothing coincides with the timing of these errors
- Checked Backups; Windows backup has been turned off completely as we use Shadowprotect. The Shadowprotect schedule is set to take incremental backups during business hours. These errors happen around the clock.
- Installed SQL server 2008 servicepack 3 and applied all available Windows updates
- Checked VSS Writer health. Intermittently, the majority of writers fail, see attached. Occasionally, all the writers come up as ok. See attached for detail of the writer fails.
- Applied the latest update for our Add2Exchange application that runs on SQL. I also had their support do a once-over on the server to check exchange health etc. Apart from a few changes to permissions to improve security, no problems were found.
- Did some due diligence and ran Google searches on a few of the errors, none of the individual solutions I found seemed to be applicable for this due to the number of different errors and I could find no real info on SQL internal backups as I think the search queries were being skewed by the word backup. I kept getting hits about Windows Backup VSS problems which don't apply here.
- SQL Server 2005 is still installed on this server as the Windows Internal Database seems to run on it. Add2Exchange also seems to rely on this. One post I found suggested I uninstall it but I am not game enough to do this without fully understanding the implications of doing this.
Can you guys please take a look at these logs for me as I am not quiet sure where to go from here.
Much appreciated
VSS-Writer-Health.txt
ASKER
Thanks for your reply deepakChauhan,
Unfortunately this does not really help me because no backups have ever been set up within any of our SQL instances.
When I log into the management studio, connect to the instances, expand out Server objects and check the backup devices and triggers folders, nothing is set in there.
Can you offer any more help on what else to check? I don't have a deep enough understanding of how SQL works to troubleshoot any sort of behind-the-scenes backup jobs or versioning.
Cheers
Unfortunately this does not really help me because no backups have ever been set up within any of our SQL instances.
When I log into the management studio, connect to the instances, expand out Server objects and check the backup devices and triggers folders, nothing is set in there.
Can you offer any more help on what else to check? I don't have a deep enough understanding of how SQL works to troubleshoot any sort of behind-the-scenes backup jobs or versioning.
Cheers
I checked your log and according to that seems your backups are being happening through third party tool.
I suspect that here is backup file location issue that's why i asked you to take the manual backup to the shared backup location where is your backup drive. It would be on the network location.
you can find the backup software information from these tables.
select top 10* from dbo.backupset
where type='D'
order by backup_finish_date desc
select top 10 * from dbo.backupmediaset order by 1 desc
You can check the SQL server error log as well if there is something related with backup failure.
I suspect that here is backup file location issue that's why i asked you to take the manual backup to the shared backup location where is your backup drive. It would be on the network location.
you can find the backup software information from these tables.
select top 10* from dbo.backupset
where type='D'
order by backup_finish_date desc
select top 10 * from dbo.backupmediaset order by 1 desc
You can check the SQL server error log as well if there is something related with backup failure.
ASKER
Hi there,
I can take manual backup of the SQL databases with the SQL management studio without issue. No backup locations exist in any of the instances and no backup schedules have ever been set up.
Shadowprotect is the installed third party backup software but that is set to take incrementals within business hours. This backup writes a single system image file to a network location.
As mentioned previously, these errors happen every 4 hours around the clock without fail so this software is not responsible for whatever is causing these errors.
Can you please explain a bit more clearly what you mean by looking at the the backup software information from tables? I'm not sure what you want me to do in these instructions;
Please assume I am a complete novice when it comes to SQL.
Cheers
I can take manual backup of the SQL databases with the SQL management studio without issue. No backup locations exist in any of the instances and no backup schedules have ever been set up.
Shadowprotect is the installed third party backup software but that is set to take incrementals within business hours. This backup writes a single system image file to a network location.
As mentioned previously, these errors happen every 4 hours around the clock without fail so this software is not responsible for whatever is causing these errors.
Can you please explain a bit more clearly what you mean by looking at the the backup software information from tables? I'm not sure what you want me to do in these instructions;
select top 10* from dbo.backupset
where type='D'
order by backup_finish_date desc
select top 10 * from dbo.backupmediaset order by 1 desc
Please assume I am a complete novice when it comes to SQL.
Cheers
You can take manual backup then no issue with databases. Now issue with your backup automation. How normally you takes the database backups?
@Can you please explain a bit more clearly what you mean by looking at the the backup software information from tables? I'm not sure what you want me to do in these instructions;
These are the SQL server tables where sql server keeps the backup logs. yusing these table we can found how frequently your backup is being taken up as you are saying it is failing every 4 hours so it means any tool or any job trying to take sql server database backup.
You can run these query in SQL server management studio and in the result set backup start date and finish date will show you when last successful backup taken and through which tool.
select top 10* from MSDB.dbo.backupset
where type='D'
order by backup_finish_date desc
select top 10 * from MSDB.dbo.backupmediaset order by 1 desc
@Can you please explain a bit more clearly what you mean by looking at the the backup software information from tables? I'm not sure what you want me to do in these instructions;
These are the SQL server tables where sql server keeps the backup logs. yusing these table we can found how frequently your backup is being taken up as you are saying it is failing every 4 hours so it means any tool or any job trying to take sql server database backup.
You can run these query in SQL server management studio and in the result set backup start date and finish date will show you when last successful backup taken and through which tool.
select top 10* from MSDB.dbo.backupset
where type='D'
order by backup_finish_date desc
select top 10 * from MSDB.dbo.backupmediaset order by 1 desc
I don't know Shadowprotect but I know that when SQL Server is running it get exclusive access to database files and that can justify the time out errors. How is configured the database backup in Shadowprotect?
ASKER
Shadowprotect takes a block-based backup of the entire volume. It is not configured to do anything specifically with SQL.
ASKER
Hi DeepakChauhan,
I ran the query on the database successfully and got the following output. Refer to the attached CSV.
Any thoughts?
A2E-Backup-Query.csv
I ran the query on the database successfully and got the following output. Refer to the attached CSV.
Any thoughts?
A2E-Backup-Query.csv
ASKER
Actually, here's a better version of the output with headers.
A2E-Query-Result.xlsx
A2E-Query-Result.xlsx
Shadowprotect takes a block-based backup of the entire volume. It is not configured to do anything specifically with SQL.Then that's why you're getting error. The application doesn't know how to handle with SQL Server files. If you run Shadowprotect with SQL Server service stopped I bet that it wouldn't return error.
Can you say to Shadowprotect to do not backup SQL files or directories?
ASKER
Then that's why you're getting error. The application doesn't know how to handle with SQL Server files. If you run Shadowprotect with SQL Server service stopped I bet that it wouldn't return error.
Can you say to Shadowprotect to do not backup SQL files or directories?
Shadowprotect is VSS aware, it does know how to deal with databases such as Sharepoint, Exchange and SQL.
This aside though, it doesn't have any impact in this case for 2 reasons; first, Shadowprotect backups only run during a 9 hour window on weekdays. Secondly, the SQL errors do not line up with the time that Shadowprotect runs. These errors occur every 4 hours around the clock.
Did you check for any job running every 4 hours?
ASKER
Did you check for any job running every 4 hours?
Hi there, that was one of the first things I did. I have combed through every scheduled task that appears in task scheduler, nothing lines up.
Then you'll need to audit the database to check what's going on.
Windows event logs shared by you indicating involvement of any third party software. This is always not necessary that thiry party tool configured on local server it can be on remote server. Like Symantec Backup Exec software. Do you have any trace mechanism to track the process which run remotely or locally at 4 hrs interval.
Or any remote backup software misconfigured.
Or any remote backup software misconfigured.
ASKER
Hi there,
Add2Exchange is the only third party software suite that runs on SQL. It is configured to do a sync of contacts in exchange public folders every 24 hours.
The backups are not responsible for this because they only run during business hours and once over the weekend each Saturday.
With regards to auditing the SQL instances, can you recommend any tools or query's that will do what I need to troubleshoot this further? I tried a full SQL Server repair this week but it has not changed anything.
Thanks
Add2Exchange is the only third party software suite that runs on SQL. It is configured to do a sync of contacts in exchange public folders every 24 hours.
The backups are not responsible for this because they only run during business hours and once over the weekend each Saturday.
With regards to auditing the SQL instances, can you recommend any tools or query's that will do what I need to troubleshoot this further? I tried a full SQL Server repair this week but it has not changed anything.
Thanks
The old way is to launch a SQL Profiler filtering to catch only the commands you want but SQL Profiler has been replaced by Extends Events, a better and with more performance way to do that.
There are some good articles in the internet to show you how to use them step by step.
There are some good articles in the internet to show you how to use them step by step.
Do one more thing, stop and disable the SQL VSS writer service and observe the log.
ASKER
Unfortunately auditing is disabled in SQL Express, any other suggestions?
Just to kill off the argument to do with 3rd party backups, I will uninstall Shadowprotect over the weekend and post back the results.
Cheers
Just to kill off the argument to do with 3rd party backups, I will uninstall Shadowprotect over the weekend and post back the results.
Cheers
Unfortunately auditing is disabled in SQL Express, any other suggestions?Which auditing are you talking about?
You have traces and extended events. Auditing is a little bit different.
Did you try to stop and disable the SQL VSS writer service on this server?
ASKER
Hi Uninstalling Shadowprotect did not make any difference, the errors are persistent.
I tried running an audit script that I found through a google search but it failed saying that auditing is disabled in SQL Express.
Any more suggestions?
Windows server backup has been configured on this server in the past and was turned off just before we virtualized it. Is it possible that remnants of it are interfering with SQL in the background? Just a thought.
I tried running an audit script that I found through a google search but it failed saying that auditing is disabled in SQL Express.
Any more suggestions?
Windows server backup has been configured on this server in the past and was turned off just before we virtualized it. Is it possible that remnants of it are interfering with SQL in the background? Just a thought.
Have you tried to stop and disable the SQL VSS writer service on this server? As i suggested you above.
ASKER
Have you tried to stop and disable the SQL VSS writer service on this server? As i suggested you above.
Sure did, no effect sorry. The errors persisted.
The server has been rebooted since as well.
ASKER
Windows server backup ran on this server prior to us virtualizing it.
Is there any chance the backup device SQL is complaining about was the USB drive we used to run these backups onto? When I turned off Windows server backup, I removed the schedule using the SBS console. Was there any further cleanup I should have done?
Just a thought.
Is there any chance the backup device SQL is complaining about was the USB drive we used to run these backups onto? When I turned off Windows server backup, I removed the schedule using the SBS console. Was there any further cleanup I should have done?
Just a thought.
ASKER
Ok, given the extended period of silence, is it worth recommending to the client that we get Microsoft Professional services in on the case?
ASKER
Hi there,
Please leave this thread open for another week. If it gets no more responses by this time next week, please close it off as I will have to recommend that we get Microsoft involved directly.
Thanks.
Please leave this thread open for another week. If it gets no more responses by this time next week, please close it off as I will have to recommend that we get Microsoft involved directly.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The issue remains unresolved
Is location accessible from the database server where backup file is going to be created.
Can you take SQL native backup to that backup location.
First try the SQL native backup like below.
Backup database <dataabase name> to disk='<location>\backupfil