SSIS: The buffer manager failed a memory allocation call

Info: 2012-03-14 10:51:01.51    Code: 0x4004800D    Source: my dataflow    Description: The buffer manager failed a memory allocation call for 65536 bytes, but was unable to swap out any buffers to relieve memory pressure. 2830 buffers were considered and 2830 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too ma ny buffers are locked. End Info

If you are using full cached mode in your data flow, you might want to review your RAM requirement.

If you are running SQL on the very same server as SSIS, consider to reduce the max server memory in order to provide Dtexec with more RAM.

Don’t forget “Instant File Initialization”

If you ever run into scenarios where a) your database files are not properly sized and b) you populate the database with gigabytes of data, performance will be degraded/reduced because you will need to wait for SQL to zero out the file while expanding it.

To mitigate this issue you have two choices. Either you size your files properly (which most DBAs and system owners don’t), or you enable “Instant File Initialization”.

I’ve used this on large implementations and it works great.

Documented here and here.

Connect BIDS 2008 R2 to a TFS 2010 server

 

• install Visual Studio 2008 R2.
• install VS 2008 Team Explorer
• install VS 2008 Service Pack 1, or reinstall.
• install the Visual Studio Team System 2008 Service Pack 1 Forward Compatibility Update for Team Foundation Server 2010

You might need to assign Port and a location to your TFS server, like this:

Useful code: Get the physical size for each table

-- Create the temp table for further querying CREATE TABLE #temp     (       rec_id INT IDENTITY(1, 1) ,       table_name VARCHAR(128) ,       nbr_of_rows INT ,       data_space DECIMAL(15, 2) ,       index_space DECIMAL(15, 2) ,       total_size DECIMAL(15, 2) ,       percent_of_db DECIMAL(15, 12) ,       db_size DECIMAL(15, 2)     ) -- Get all tables, names, and sizes EXEC sp_msforeachtable @command1 = "insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",     @command2 = "update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)" -- Set the total_size and total database size fields UPDATE  #temp SET     total_size = ( data_space + index_space ) ,         db_size = ( SELECT  SUM(data_space + index_space)                     FROM    #temp                   ) -- Set the percent of the total database size UPDATE  #temp SET     percent_of_db = ( total_size / db_size ) * 100 -- Get the data SELECT  * FROM    #temp ORDER BY total_size DESC -- Comment out the following line if you want to do further querying DROP TABLE #temp

SSIS: The Balanced Data Distributor

This little friend is worth looking at:

“Microsoft® SSIS Balanced Data Distributor (BDD) is a new SSIS transform. This transform takes a single input and distributes the incoming rows to one or more outputs uniformly via multithreading. The transform takes one pipeline buffer worth of rows at a time and moves it to the next output in a round robin fashion. It’s balanced and synchronous so if one of the downstream transforms or destinations is slower than the others, the rest of the pipeline will stall so this transform works best if all of the outputs have identical transforms and destinations. The intention of BDD is to improve performance via multi-threading. Several characteristics of the scenarios BDD applies to: 1) the destinations would be uniform, or at least be of the same type. 2) the input is faster than the output, for example, reading from flat file to OleDB. “

SQLCAT has described it further here.

Download here.