View Message Thread (8 replies)
| 64mb limit for linked server selects? |
 |
From: BitBuster
Date Posted: 6/28/2010 2:51:00 AM
Dear everyone,
We have a linked server connection between two servers (MS SQL Server
2005), but we have problems transferring large text fields across this
connection. That is to say, INSERT is fine, but SELECT replaces large
text fields (> 64mb) with an empty string - with no warning.
Do you know if this is an SQL Server or a network setting?
And (more importantly) do you know how this limit can be increased?
Thanks,
Ivar
| Re: 64mb limit for linked server selects? |
|
From: Erland Sommarskog
Date Posted: 6/28/2010 3:15:00 PM
BitBuster ( ivarru@gmail.com) writes:
> We have a linked server connection between two servers (MS SQL Server
> 2005), but we have problems transferring large text fields across this
> connection. That is to say, INSERT is fine, but SELECT replaces large
> text fields (> 64mb) with an empty string - with no warning.
>
> Do you know if this is an SQL Server or a network setting?
> And (more importantly) do you know how this limit can be increased?
I ran this on my servers at home:
declare @bobben varchar(MAX)
select @bobben = res
from openquery(YAZORMAN, 'SELECT replicate(convert(varchar(MAX),
''1234567890''), 7000000) as res')
select datalength(@bobben)
select @bobben = replicate(convert(varchar(MAX), '1234567890'), 7000000)
select datalength(@bobben)
Both selects returned 70000000 (70 millions), which is > 64 MB.
You mention "text". There are a lot of restrictions with the text
data type. There is also the setting SET TEXTSIZE which may be set
by the OLE DB provider to 64 MB.
If you are using the text data type, I stronly recommend that you
move to the new data type varchar(MAX), which is the same basic idea
as text, but which in difference to text is a first-class citizen.
With varchar(MAX) you done need READTEXT and all that jazz.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
| Re: 64mb limit for linked server selects? |
|
From: BitBuster
Date Posted: 6/29/2010 4:30:00 AM
On 28 Jun, 23:15, Erland Sommarskog < esq...@sommarskog.se> wrote:
> I ran this on my servers at home:
>
> declare @bobben varchar(MAX)
> ...
Thanks, but I guess I should have been more precise.
In fact, I am using the data type nvarchar(max) and the value being
truncated has length > 66000000 (i.e. > 128mb).
I have no problem selecting 70mb from a varchar(max) column.
Kind regards,
Ivar
| Re: 64mb limit for linked server selects? |
|
From: Erland Sommarskog
Date Posted: 6/29/2010 3:15:00 PM
BitBuster ( ivarru@gmail.com) writes:
> On 28 Jun, 23:15, Erland Sommarskog < esq...@sommarskog.se> wrote:
>> I ran this on my servers at home:
>>
>> declare @bobben varchar(MAX)
>> ...
>
> Thanks, but I guess I should have been more precise.
>
> In fact, I am using the data type nvarchar(max) and the value being
> truncated has length > 66000000 (i.e. > 128mb).
> I have no problem selecting 70mb from a varchar(max) column.
OK. I changed my script to use nvarchar instead, and I get back
140000000.
Not that this proves anything. I guess your query looks slightly
different. (To put it mildly.) But without a repro, it's difficult to assess
the issue.
But let's try to narrow it down a bit:
o What happens if you run my stupid test query:
declare @bobben nvarchar(MAX)
select @bobben = res from openquery(SERVER, 'SELECT
replicate(convert(nvarchar(MAX), ''1234567890''), 7000000) as res')
select datalength(@bobben)
o Which version of SQL Server do you use for the local server? The
remote server? (The remote is also SQL Server, right?)
o Which editions? 32-bit or 64-bit?
o The remote server is really remote I presume? (Mine is a second
instance on the same box.)
Also, when you run your query, can you run Profiler on the remote
query, and add the Errors and Warnings event category to the trace?
My thinking that this is some resource constraint, that may leads to
an error which for some reason is not reported, but I'm just speculating.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
| Re: 64mb limit for linked server selects? |
|
From: BitBuster
Date Posted: 6/30/2010 10:18:00 AM
On 29 Jun, 23:14, Erland Sommarskog < esq...@sommarskog.se> wrote:
>
> o What happens if you run my stupid test query:
>
> declare @bobben nvarchar(MAX)
> select @bobben = res from openquery(SERVER, 'SELECT
> replicate(convert(nvarchar(MAX), ''1234567890''), 7000000) as res')
> select datalength(@bobben)
>
> o Which version of SQL Server do you use for the local server? The
> remote server? (The remote is also SQL Server, right?)
I get 0 from both my linked servers!
(... but if I write 3000000 instead of 7000000, the result is
60000000.)
One is a SQL Server 2008 Express Edition on a (virtual) 64 bit
Windows Server 2008 located in a so-called DMZ in the same building.
The other is a SQL Server 2005 Standard Edition on a (virtual) 32 bit
Windows Server 2003 somewhere far away.
My local server is similar (to the second one).
> Also, when you run your query, can you run Profiler on the remote
> query, and add the Errors and Warnings event category to the trace?
I will try that tomorrow. Thanks for your patience!
--
Ivar
| Re: 64mb limit for linked server selects? |
|
From: Erland Sommarskog
Date Posted: 6/30/2010 4:12:00 PM
BitBuster ( ivarru@gmail.com) writes:
> I get 0 from both my linked servers!
> (... but if I write 3000000 instead of 7000000, the result is
> 60000000.)
>
> One is a SQL Server 2008 Express Edition on a (virtual) 64 bit
> Windows Server 2008 located in a so-called DMZ in the same building.
> The other is a SQL Server 2005 Standard Edition on a (virtual) 32 bit
> Windows Server 2003 somewhere far away.
> My local server is similar (to the second one).
OK, I was able to reproduce it, and I also have an idea what is
going on.
The problem occurs on the local server, not the remote server. The
keyword is 32-bit. On a 32-bit server there is by necessity some
restrictions with memory. 32-bit SQL Server is able to access more
than 4GB of memory through AWE, but this can only be used for the
buffer pool. This means that everything else must be in the regular
address space. It is not uncommon to run out of this memory.
There is a certain area known as memtoleave, which is used for various
things like memory for the OLE DB provider. By default this memory is
256 MB. You can increase it with the server option -g; I tried this,
but on my server at least it did not help. This may simply be a hard
limit.
On the other hand, if I run the batch from a 64-bit machine, I get
back 140 millions - even if the remote server is 32-bit.
I also ran the test on a virtual machine with only 516 MB of memory in
total. In this case the batch produced an error. I would suggest that
in the case we get back 0, this is a bug; an error message should be
produced.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
| Re: 64mb limit for linked server selects? |
|
From: Erland Sommarskog
Date Posted: 6/30/2010 4:15:00 PM
| Re: 64mb limit for linked server selects? |
|
From: BitBuster
Date Posted: 7/3/2010 5:14:00 PM
On Jul 1, 12:11 am, Erland Sommarskog < esq...@sommarskog.se> wrote:
> OK, I was able to reproduce it, and I also have an idea what is
> going on.
> [...]
> This may simply be a hard limit.
>
> On the other hand, if I run the batch from a 64-bit machine, I get
> back 140 millions - even if the remote server is 32-bit.
OK. Until we upgrade our servers, I will avoid the problem using
compression (gzip).
> I also ran the test on a virtual machine with only 516 MB of memory in
> total. In this case the batch produced an error. I would suggest that
> in the case we get back 0, this is a bug; an error message should be
> produced.
I agree. Will you inform Microsoft?
On Jul 1, 12:15 am, Erland Sommarskog < esq...@sommarskog.se> wrote:
> One more thing: I didn't see this first, but there is a big fat error
> message in the SQL Server error log on the localserver about
> PAGE_FAIL_ALLOCATION.
Strange. I did not see any such entries in my logs
(and I can not find any info on PAGE_FAIL_ALLOCATION on the web).
Did this happen when you tried with 516 MB of memory?
--
Ivar
| Re: 64mb limit for linked server selects? |
|
From: Erland Sommarskog
Date Posted: 7/4/2010 2:26:00 AM
BitBuster ( ivarru@gmail.com) writes:
> On Jul 1, 12:11 am, Erland Sommarskog < esq...@sommarskog.se> wrote:
>> I also ran the test on a virtual machine with only 516 MB of memory in
>> total. In this case the batch produced an error. I would suggest that
>> in the case we get back 0, this is a bug; an error message should be
>> produced.
>
> I agree. Will you inform Microsoft?
I submitted
https://connect.microsoft.com/SQLServer/feedback/details/573055/query-
against-linked-may-return-incorrect-result-when-memory-runs-out
but to be honest, I don't really expect them to fix it. It's after
all quite a crazy thing to do on a 32-bit machine.
> On Jul 1, 12:15 am, Erland Sommarskog < esq...@sommarskog.se> wrote:
>> One more thing: I didn't see this first, but there is a big fat error
>> message in the SQL Server error log on the localserver about
>> PAGE_FAIL_ALLOCATION.
>
> Strange. I did not see any such entries in my logs
> (and I can not find any info on PAGE_FAIL_ALLOCATION on the web).
> Did this happen when you tried with 516 MB of memory?
Both. But I messed up the error code, it's FAIL_PAGE_ALLOCATION. Here is
an extract from one my logs:
2010-06-30 23:12:47.81 spid53 Failed allocate pages:
FAIL_PAGE_ALLOCATION 17090
2010-06-30 23:12:47.84 spid53
Memory Manager KB
---------------------------------------- ----------
VM Reserved 554912
VM Committed 158480
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
2010-06-30 23:12:47.85 spid53
Memory node Id = 0 KB
Note that the message occurs in the log on the server where you run
the query.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Would you like to track this thread?
By adding this News Thread to your Favorites Area you can refer to it later with just a click of the mouse.
Also you can optionally be notified instantly whenever there are any replies
posted to this Thread. To add this Thread to your Favorites Area just click on the red arrow next to the subject of the thread above
 .
|
