BACKUP DATABASE MAXTRANSFERSIZE default

Question

Post reply

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: 1211 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: 2675 More actions · Answer 2

Joshua M Perry

SSCrazy

Points: 2675

September 7, 2006 at 8:12 am

#659099

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.