buffer not large enough

Category: sql server getstarted

Question

mtm81 on Thu, 09 Jul 2009 11:21:41


Having recently upgraded to use SQL 2005 Expres edition I am finding a little issue whereby when inserting into one specific table... nulls values are being inserted instead of the values being passed to it (from an ASP page but that is not relavent for this question)

I've checked the db using checkDB and also checked the specific table in question without any errors.
The only thing I've noticed is that if I try to open the table fully using SQL Server 2005 Management tools I get the error saying "buffer too small" etc..

The table is relatively large with around 35k records however I don't think it should have a problem opening?

I've looked online and found the hotfix for SP2 for the SQL 2005 but that seems to relate to queries using temp tables.. of which I'm not.

Anyone got any ideas?

thanks for any replies...

Replies

Jens K. Suessmeyer - on Thu, 09 Jul 2009 15:36:44


If values are inserted that are not expected to be inserted, this can be based upon several things:

  • Your application does not pass the values (Check profiler and the statements coming in)
  • Your table has a trigger discarding the values
  • You are passing the values through a stored procedure which discards the values during the insert


    Make also sure that you really check the table you are inserting to and that it is not a table from another schema or even a view which abstracts the view on the table.

-Jens

mtm81 on Thu, 09 Jul 2009 16:00:42


Hi,
thanks for the reply - I don't think I made myself clear.

I have a feeling there is an issue with the table as it doesn't happen all the time.

I will check the things you have said.. however my main problem seems to be that it's just starting happening since I upgraded to Server 2005 and also I cannot open the table using the query tool in 2005 management as I descrived above.

however the checktable command doesn't show any problems!!

Sankar Reddy on Fri, 10 Jul 2009 18:32:49


CHECKDB and CHECKTABLE help in identifying and resolving corruption and in this case I am not sure you are affected by that.

Managment Studio has some weird issues and its important that you upgrade to Service Pack 2 or Service Pack 3. Its better to always use Transact SQL to look at the data instead of the GUI.

And 35K records relatively is not large enough at all for SQL Server and to express.

mtm81 on Mon, 13 Jul 2009 09:33:31


Hi,
I have ran CheckDB adn CHECKTABLE on the specific db and specific table without any errors. however there is still this issue. (or two issues dependant on how you look at it.

the first issue is the intermittent insertion of blank records instead of the values being passed as a part of an SP.

the second is not being able to actually open the table to look at the data using something like the GUI.

since your post I've tried using Transact SQL using the query analzer to open the specific table and I get the following error:

"Communitcation Link Failure"..
all other tables in the database open without issue.. and some have more records than this.

I can tell there is some kind of corruption in the table but the checks are not showing anything.

Anyone got any ideas?

cheers

mtm81 on Wed, 15 Jul 2009 12:09:03


I've got more info... I'm finding that other scripts that run to update records on this table are also intermittently failing to update.

How do I check this table to fix the errors it clearly has, when all the obvious ways to fix it aren't working?

Ed Price - MSFT on Sat, 04 May 2013 08:35:17


Still an issue?

Thanks!