I have 25 databases that I need to combine into 1 database. At first I tried to create a ssis package that would copy all the data from each table to one place, but then I got an error:
Information: the buffer manager refused to allocate memory for 10485760 bytes, but failed to replace any buffers to relieve memory pressure. 1892 buffers were reviewed, and 1892 were blocked. Either there is not enough memory available for the pipeline because there is not enough, other processes have used it, or too many buffers are blocked.
Then I realized that this is not a good idea, and that I only need to insert new records and update existing ones. After that I tried this option:
- Get a list of all contacts. strings
- foreach db, copy the new entries and update the existing ones (those that need to be updated, copy from the source to the temp table, delete them from the destination and copy from temp to the destination table)
Here, how a data flow task looks like
In some cases, a data stream processes more than a million rows. BUT, I still get the same error - out of memory.
In the task manager, the following situation:
I should note that 28 databases are replicated on the same server, and when this package is not running, the sql server still uses more than 1 GB of memory. I read that this is normal, but now I'm not sure that ...
SQL Server, : http://support.microsoft.com/kb/977190
...
- , , , ?
,
Ile