Msg 7412, Sev 16, State 1, Line 75

Question

Msg 7412, Sev 16, State 1, Line 75

CharlieL

Mr or Mrs. 500

Points: 509
More actions
January 27, 2012 at 8:47 am

#249939

We're trying to run a BizTalk backup, and it is failing with the below error:
Msg 7412, Sev 16, State 1, Line 75 : OLE DB provider "SQLNCLI10" for linked server " " returned message "Query timeout expired". [SQLSTATE 01000]
This was running successfully until last night. No settings have been changed, the drive it backs up onto has enough disk space, and when we run a backup on the individual database it is failing on, it works...
Any ideas?

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

MyDoggieJessie SSC-Forever Points: 44519 More actions · Answer 1

How busy is the server during the time of failure? If there's a lot of connections, I/O or CPU utilization going on, I've seen (many times) that specific driver timeout because it can't get a connection.... running the exact same thing when the server is practically idle and it never fails.

What version of SQL?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

Few articles here for investigating the issue...

Error message when you execute a linked server query in SQL Server: "Timeout Expired"

http://support.microsoft.com/kb/314530

Troubleshooting: Timeout Expired

http://msdn.microsoft.com/en-us/library/ms190181.aspx

CharlieL Mr or Mrs. 500 Points: 509 More actions · Answer 3

Hi,

There isn't an issue with connections, CPU or I/O, we kept an eye on these during the backup and there was nothing concerning.

The backup that first failed was at midnight, when the server is quiet, so I don't think it's down to the server being busy.

The version is Microsoft SQL Server 2008 R2 (RTM) - 10.50.1753.0 (X64)

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 4

CharlieL (1/27/2012)
The backup that first failed was at midnight, when the server is quiet, so I don't think it's down to the server being busy.

Wait. You're using a Linked Server connection to backup a database?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

CharlieL Mr or Mrs. 500 Points: 509 More actions · Answer 5

The linked server is actually the same server that the databases are held on.

It isn't something that we've set up, it looks like it was done as part of the BizTalk Installation.

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 6

Are your backups native SQL backups or are they using BizTalk to backup?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

CharlieL Mr or Mrs. 500 Points: 509 More actions · Answer 7

CharlieL

Mr or Mrs. 500

Points: 509

January 27, 2012 at 9:35 am

#1439476

We're using the BizTalk backup stored procedures

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 8

I'm reaching, but ... Does this help? http://guomingli.blogspot.com/2010/03/ole-db-provider-sqlncli-for-linked.html

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

CharlieL Mr or Mrs. 500 Points: 509 More actions · Answer 9

Thanks for the link 🙂

Unfortunately we already have the remote query timeout value set to 0, as all of our BizTalk databases are on the same database server, so it isn't that causing the issue.

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 10

I tried calling for help, but I guess no one uses BizTalk for database backups around here. Have you tried on Microsoft's site to see if one of their peeps can help?

Or maybe tweet the link to this post with the hashtag #biztalk?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.