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/file/d/0B-yTIGptVzeoZHVXSkdlQmhDaEU/view?usp=sharing

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
LVL 3
TingathewingaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
Backup are failing for every database on the server.

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>\backupfilename.bak'
0
TingathewingaAuthor Commented:
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
0
Deepak ChauhanSQL Server DBACommented:
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.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

TingathewingaAuthor Commented:
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;

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
0
Deepak ChauhanSQL Server DBACommented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
TingathewingaAuthor Commented:
Shadowprotect takes a block-based backup of the entire volume. It is not configured to do anything specifically with SQL.
0
TingathewingaAuthor Commented:
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
0
TingathewingaAuthor Commented:
Actually, here's a better version of the output with headers.
A2E-Query-Result.xlsx
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
TingathewingaAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you check for any job running every 4 hours?
0
TingathewingaAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then you'll need to audit the database to check what's going on.
0
Deepak ChauhanSQL Server DBACommented:
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.
0
TingathewingaAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Deepak ChauhanSQL Server DBACommented:
Do one more thing, stop and disable the SQL VSS writer service and observe the log.
0
TingathewingaAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Deepak ChauhanSQL Server DBACommented:
Did you try to stop and disable the SQL VSS writer service on this server?
0
TingathewingaAuthor Commented:
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.
0
Deepak ChauhanSQL Server DBACommented:
Have you tried to stop and disable the SQL VSS writer service on this server?  As i suggested you above.
0
TingathewingaAuthor Commented:

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.
0
TingathewingaAuthor Commented:
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.
0
TingathewingaAuthor Commented:
Ok, given the extended period of silence, is it worth recommending to the client that we get Microsoft Professional services in on the case?
0
TingathewingaAuthor Commented:
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.
0
TingathewingaAuthor Commented:
Ok, I'm going to recommend that we get Microsoft support involved with this one.

Thanks for the attempted assistance.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TingathewingaAuthor Commented:
The issue remains unresolved
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SBS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.