I am building a cube in SQL Server Analysis Services which is using the local SQL Server instance as its data source. The bulk of the data in the data source is a large fact table (~200GB) which is too large for the local hard drive, so I moved the .mdf and .ldf files for that database to an external USB hard drive. I want to keep these files on the external hard drive but store the processed cube on the local hard drive. My hope is/was that while it will make cube processing slow, the cube querying will still be fast when clients connect

And when I can process the cube, this works: the cube querying is still fast because the cube queries don't hit the slow external USB hard drive. The problem I am having is that often when I go to process the cube, I get errors like this:

"OLE DB error: OLE DB or ODBC error: Time-out occurred while waiting for buffer latch type 2 for page (1:1785), database ID 5.; 42000

Going to the event log, I see errors like these:

SQL Server has encountered 29351 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [U:\sqldata\X.mdf] in database [X] (5). The OS file handle is 0x0000000000000164. The offset of the latest long I/O is: 0x00002f4b664000

This seems to be happening because the USB hard drive is slow. I don't mind the queries needed for processing taking a long time, but it seems like SQL Server has a hard limit for I/O requests. Is there a way around this?

4 Answers 4


You can turn this behavior off with trace flag 830, for more information check this article on msdn

dbcc traceon(830, -1)

or as a start-up parameter if you want this turned on permanently: add /T830 as a service start-up parameter.

Seeing as you have such cheap disks & your running sql server 2008, take a look at turning on page compression for the whole database, you will have a small CPU overhead, but I'd say in your situation the pro's outweigh the cons.


You could also check the disk for fragmentation ? That might speed it up a bit.

(obviously, actually defragging the disk, rather than just checking it, will speed it up....)


Since you are running SQL Server 2008, I would suggest looking in to using Data Compression on your Datawarehouse tables (if indeed you are using a DW, which I assumed from your wording). It may be possible to place your fact table on the higher speed drive(s) using this tech.


Make sure you have excluded your database files from any antivirus checking, this can kill performance on large files .

You must log in to answer this question.