While processing large cubes, various file system errors: "the background thread running lazy writer encountered an I/O error"

Question

 

We consistently get these I/O errors when processing large OLAP databases.  After a reboot, I can usually get a database to process a few times, but then suddenly reprocessing will fail with errors similar to the one shown below, when nothing changed (no data or structure changes).  The specific file(s) it reports are always different.  Also, sometimes it crashes the msmdsrv.exe process, which seems to intermittently affect other server functions as well (killing the ability to RDP into the server, or the ability to open Event Viewer).

 

<error message>

File system error: The background thread running lazy writer encountered an I/O error.
Physical file: \\?\D:\OLAPData\SSAS1\SA1.166.db\SA1 Data Source View 1.238.cub\Fact Subject.231.det\Fact Subject.221.prt\271.Aspect.Source Key.fact.map.hdr.
Logical file: .
File system error: The following error occurred while writing to the file 'LazyWriter Stream':
Insufficient system resources exist to complete the requested service. .
File system error: The background thread running lazy writer encountered an I/O error.
Physical file: \\?\D:\OLAPData\SSAS1\SA1.166.db\SA1 Data Source View 1.238.cub\Fact Subject.231.det\Fact Subject.221.prt\271.Aspect.Source Key.fact.map.hdr.
Logical file: . File system error: The following error occurred during a file operation: .
Errors in the OLAP storage engine: An error occurred while processing the indexes for the Fact Subject partition of the Subject measure group of the SA1a cube from the SA1 database.
</error message>

 

We are running:

• SQL Server 2005 SP2 with cumulative update pack 10 (9.00.3294), the problem also occurred with just SP2 (9.00.3042), which is why we tried the CU10 pack.
• Windows 2003 Server R2 Enterprise SP2
• 2 dual core 3GHz CPUs
• 15GB RAM (with /3GB switch, not using AWE)
• DELL PERC 6/i RAID controller
• C: drive hosts the operating system and has 59GB of free space
• D: drive hosts the sql program files and the data files, it has 68GB of free space
• The server hosts 3 instances of SQL and 2 instances of SSAS.

The problem occurs on two SSAS databases and can be replicated on two servers (although both configurations feature the same disk subsystem).

 

The larger SSAS database is 420MB when fully processed and the memory used by msmdsrv.exe never goes above 1.7GB during successful reprocessing.  The smaller is around 150MB when processed.  Both databases contain one large cube comprised of 10 to 15 fact tables and around 100 dimensions.

 

Things we tried:

• Ran chkdsk and fixed the errors it found.
• Many permutations of LowMemoryLimit/TotalMemoryLimit and Process-related SSAS properties.
• Limiting the server to one SQL and one SSAS instance.
• Turning off the /3GB switch.

Not sure where to go from here. Any ideas?

Answer 1

Hi,

 

I quote: "Both databases contain one large cube comprised of 10 to 15 fact tables and around 100 dimensions"

 

MS recommendations says 10-15 dimensions and 2-3 fact tables in a cube, according to the SSAS 2005 Performance Guide.

 

Are you running these large cubes on 32-Bit or 64-Bit CPUs and are you using a SAN or not?

 

Anyway I think it is the size of the cubes(number of fact tables and the number of dimensions x the number of members in each dimension) that is the main problem.

 

HTH

Thomas Ivarsson

 

Answer 2

Thanks for your response.

 

This is a 32-bit server with local RAID SCSI storage (Dell PERC 6i controller).

 

The same database reprocesses just fine on another of our servers, with the exact same data.  That server has less RAM (4GB, and no /3GB switch) and slightly slower CPUs.  The main difference though is that they're using a different RAID control (Dell PERC 4e/Di RAID controller).

 

I hear what you are saying about the cube being too big, and we're basically trying to figure out if a redesign is the right solution.  That is to say, is this problem a direct result of the design, the hardware or both?

 

 

Answer 3

Hi Matt,

 

I do not have a hardware lab, I wish I would, but I assume that these two cubes should be redesigned.

 

If you feed these cubes with views or named queries(SSAS) the problem with I/O is  what you can expect.

 

It is mainly the design that is the problem. 64-bit CPUs, SAN and a lot of memory might help but it is not that important.

 

With a 32-bit system you will have 2 GB of RAM available for SSAS 2005.

 

Today I always recommend 64-bit for all cubes.

 

HTH

Thomas Ivarsson

 

Answer 4

We were eventually able to resolve this issue by simply removing the /3GB switch and turning ON the /PAE switch.  All SSAS properties are back to their defaults.

We're now able to reprocess these large cubes without any problems, so even though the design may not be ideal, Analysis Services can still handle it just fine.

 

Answer 5

Hi,

I was also facing same issue

"Error 12 File system error: The background thread running lazy writer encountered an I/O error. Physical file: \\?\C:\Program Files\Microsoft SQL Server\MSAS10.SQLSERVER2008\OLAP\Data\CTS_Adventure_DB_CUBE.0.db\CUbe_Adventure_Works_DW.0.cub\Fact Reseller Sales.0.det\Fact Reseller Sales.0.prt\1.fact.data. Logical file: .  0 0"

I Was facing this issue becuase of less space in C Drive (Database Exist). I just made free space and Reprocessed Cube It was working fine.

Thanks for Ans:)

---

Thanks Shiven:)