Time out ... waiting for buffer latch

[MikeLacey]

Hi,

I'm inserting a few rows (4.8 million) into a SQL Server 2000 database running on a PC running Windows 2000 Server with 256mb RAM.

The inserts are being done via Microsoft's ODBC driver, feeding the driver is a Perl v5.6.1 script using the DBI.

This mass insert has been running a while now and every 3/4 of a million or so records I get the following error message and no inserts occurr for 20 minutes or so:

"Time-out occurred while waiting for buffer latch type 2 for page (1.105), database ID 7. (SQL-37000)"

There are no other processes using the database while the insert is taking place.

During normal inserts Task Manager shows the CPU split between Perl (65%) and SQL/Server (35%). While this is taking place Task Manager shows Perl at 0% and SQL/Server mostly 0 but sometimes around 17% or 20%.

I'm not really a SQL/Server sort of chap, so I'd appreciate any help or suggestions anyone has.

Regards, Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[tlbroadbent]

Hi Mike,

The error is new to me. There are some KB articles about "buffer latch" errors. These articles are about SQL versions 6.5 through 2000 Enterprise Edition. The "fix" may depend on which version you are using.

SQL 2000 Books Online indicates that this error can be ignored as long as the server continues. See

http://msdn.microsoft.com/library/en-us/trblsql/tr_reslsyserr_1_2a45.asp.

Neil Pike has an entry in the SQL FAQ regarding the error. He indicates it occurs because the disk subsystem cannot keep up with the level of activity. However, he also says that this may indicate problems with the disk subsystem. See

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=14136.

--------------------------------

Are you inserting one record at per SQL batch or multiple records? Do you do an explicit Begin Transaction? Do you commit batches of rows? That is do you commit after every X inserts?

Do you see any additional errors in the SQL Error log or NT Event log? How large is the Transaction Log for the database? How large is the database? How much disk space remains on the drive where the DB exists? What kind of disk subsystem do you have? Are the database and log on the same disk?

You mentioned that no other process is using the database. Are other processes running that could be using the disk? Backups or other I/O intensive activities on other databases could contribute to the problem. Even non SQL activities on the disk could contribute to the problem.

These are just some thoughts that come to mind - mostly questions as you can see. Let me know if if you find a solution. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[MikeLacey]

Terry,

Thanks for your careful reply.

The comment regarding high disk activity sounds relevant as the disk is running at an average of 85% utilization when the insert is running.

With your comments in mind I am trying the following:

Move the source text file to another disk

Apply SP2 to SQL/Server (yes, I know, should have done this before I even asked the question - sorry)

-----------------------------------

Answers to your questions:

Are you inserting one record at per SQL batch or multiple records?

Inserting a single row at a time.

Do you do an explicit Begin Transaction?

yes

Do you commit batches of rows? That is do you commit after every X inserts?

Commit every 1000 rows

Do you see any additional errors in the SQL Error log or NT Event log?

No, none.

How large is the Transaction Log for the database?

345mb

How large is the database?

7.3gb

How much disk space remains on the drive where the DB exists?

23gb

What kind of disk subsystem do you have?

Single 40gb disk

Are the database and log on the same disk?

yes

Mike
"Experience is the comb that Nature gives us after we are bald."

Is that a haiku?
I never could get the hang
of writing those things.

 

[tlbroadbent]

It appears that using a single drive for import file, database and log could lead to an IO problem. Do you have any additional drives on the server where you could move the transaction log? Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[MikeLacey]

No, but I've moved the import file to another drive and that seems to have solved (or rather masked) the problem.

When I left it this morning it was inserting about 300 records per second and neither disk was maxed out.

<wry smile> When this project is done I'm back off to Oracle land I think...

Thanks Terry, much appreciated.

Regards, Mike
&quot;Experience is the comb that Nature gives us after we are bald.&quot;

Is that a haiku?
I never could get the hang
of writing those things.