BACKUP DATABASE MAXTRANSFERSIZE default

Question

BACKUP DATABASE MAXTRANSFERSIZE default

TF-245998

Mr or Mrs. 500

Points: 587
More actions
September 6, 2006 at 9:36 am

#62039

Would anyone know what the default MAXTRANSFERSIZE is for SQL Server 2000?

On a Windows 2003 server, 4 processor, SQL Server 2000 SP4, using EVA 8000 SAN, we're sporadically experiencing messages like this:
SQL Server has encountered 16 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [H:\MSSQL\Data\tempdb_Data_05.NDF] in database [tempdb] (2). The OS file handle is 0x000005B8. The offset of the latest long IO is: 0x00000040a6e000
We have several SQL Servers attached to the SAN, and the messages will sometimes appear on one server when another one is running database backups to disk.

I found knowledgebase article 897284 and asked our server admins to look into it. They opened a ticket with HP, and HP is saying that the BACKUP DATABASE default MAXTRANSFERSIZE is 4 mb. for SQL Server 2000, and want us to specify MAXTRANSFERSIZE of 128k in our database backups. We're having a hard time finding clear documentation on what the default MAXTRANSFERSIZE actually is. Article 904804 seems to indicate that it is 1 mb.

Any assistance clearing this up would be appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Shawn Wilson Ten Centuries Points: 1271 More actions · Answer 1

Are your servers configured for NIC Teaming? If so, try breaking them and see what happens. We've had a similar case during restores of large databases 300-400 GB and seeing your mentioned error plus various other "no sufficient resources available" errors. We disabled the NIC teaming and saw the problem go away.

I've also seen this happen quite frequently whenever a disk drive fails and a rebuild kicks in. Check to see if there have been any drive failures recently. We had a bad array enclosure which apparently would blow out a drive every couple of days. HP replaced the backplane of that enclosure and we haven't had any drive failures since. (knockknock)

Joshua M Perry SSCrazy Points: 2676 More actions · Answer 2

I had this problem and turning off the /3GB switch fixed it.

Joshua Perry
http://www.greenarrow.net

TF-245998 Mr or Mrs. 500 Points: 587 More actions · Answer 3

Passed this on to our server guy, he said we use NIC Teaming, but only in a smart failover configuration, not load balancing. No drive failures.

8 SAN attached servers are involved, with memory sizes ranging from 3 GB to 16 GB, so we want to use as much memory as possible for SQL Server.

I emailed a performance & tuning instructor, he didn't know what the MAXTRANSFERSIZE default is either, but suspects it's memory dependent.

Anyway, HP is having us test running backups with MAXTRANSFERSIZE parameters & preferred path settings, to try to avoid huge I/Os suspected of causing the timeouts.

TF-245998 Mr or Mrs. 500 Points: 587 More actions · Answer 4

Oops, I meant to say he suspects the MAXTRANSFERSIZE default is hardware dependent, not memory dependent.

Shawn Wilson Ten Centuries Points: 1271 More actions · Answer 5

I would check the event log for any errors or warnings related to NIC teaming. If there are any, this may very well be a probable cause.

TF-245998 Mr or Mrs. 500 Points: 587 More actions · Answer 6

TF-245998

Mr or Mrs. 500

Points: 587

September 8, 2006 at 11:31 am

#659418

Nothing in the event logs anything like that.