July 30, 2020 at 12:46 pm
I have created a job to randomize some data in the lower environments after a restore from production server backup. The updated statements are generated dynamically and stored in a "config" table and looped through and run (exec (@UpdateStmt);)
This worked against all the database in all (expect one database in one environment).
UPDATE TBL set [City] = N.city from [dbo].[daSomeTable] TBL
INNER JOIN Workarea.dbo.SAMPLE_CITY_STATE_ZIP N on Cast(Cast(right(CompanyID,5) as varbinary) as int)%42000 = N.id
where len(ltrim(rtrim(TBL.[City]))) > 0
There are around 42000+ random sample addresses in all environments. The only difference is that the database that it fails has more rows compared to other environments/databases (1,346,440 rows)
The error that I am getting is:
Attempting to grow LOB beyond maximum allowed size of 2,147,483,647 bytes
I compared the length of the data in the existing column thats being updated and the sample data and the sample data size is less than the data in the table being updated.
I would really appreciate if any one can provide any pointers. Is there any db setting/server setting thats preventing this updates from working?
Thanks
Sreejith
July 30, 2020 at 2:16 pm
My first step would be to compare datatypes on a lot of that. For example, what is the datatype of TBL.City and N.City and N.CompanyID (guessing that is N.CompanyID and not TBL.CompanyID), and N.id?
Looking at the names of those, I am not expecting any of those to be LOB's unless one of your City values is a VARCHAR(MAX), but even then, if it works on one table and you are not appending data to it, it should work on the other UNLESS N.City is a VARCHAR(MAX) and the TBL.City is an NVARCHAR(MAX), then you could get that error, but I can't imagine that you are storing city names that are 2 GB in size.
If they are both VARCHAR(MAX) data types, then there is no reason that I can see for that to fail. If TBL.City is an NVARCHAR(MAX) though and N.City is VARCHAR(MAX), you may need to trim or tweak your N.City column to fit in the NVARCHAR(MAX) if the length is very long.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 30, 2020 at 5:25 pm
Just an update. Just a plain Update TableName set Col ='xyz' is failing
The server does have enough space, there is no restriction on max file size for data or log files. I am kinda lost.
July 30, 2020 at 5:42 pm
Are you getting the same error? If so, my next thought is a trigger on the table is doing something silly.
But, just to address potential other pain points, how large is the database file(s)? how large is the log file? how full is the database file(s)? how full is the log file? How full is tempdb (files and log)? What about space on the physical disk? If you can afford (due to the performance hit to the server, not financially) to do it, does checkdb come back with any errors? Any other errors in the SQL Logs?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 30, 2020 at 11:01 pm
Once again, I'll agree with Brian. Seems likely that a trigger on the table being updated might be doing something that tracks changes, and possibly to a varchar(max) or nvarchar(max) column, as perhaps it's NOT the columns you are updating that is the problem.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply