Autogrowth, no space available and truncated data

SQL Server Administration
1

Hi guys,

I'm running SQL server 2008 with sp2. The database was created about 10 years ago and all has been fine, until yesterday when it started doing some weird stuff. One of my stored procedures started randomly truncating data. After checking my code and testing, I finally dropped the table, recreated it and it's still doing the same thing. The field is varchar(MAX) and it sometimes truncates after character 30, sometimes 27, sometimes 28 depending on the text sent to it, but each time it truncates at some length.

I then checked the properties of the database and see that the size is 19Mb for the mdf and 1Mb for the ldf. The mdf file is set for autogrowth and the ldf file is set to limited. The properties page says there is 0mb space available. I changed both to autogrowth, increased the db size, saved and rebooted the server. After the reboot the properties page for the db hasn't saved the change for autogrowth on the ldf, and I still get the data truncation.

I also tried shrinking the db in hopes it would resolve the available space issue, and although it reduced the db size by a few Mb it still says 0Mb available.

Not sure if the two things are related or not, but I'd obviously like fix the issue(s).

Any suggestions would be much appreciated.

Thanks!

2

I don't think so.

My normal reaction to that is "what changed yesterday"? :slight_smile:

Either it is software (someone, perhaps not you, changed something)

Or it is hardware - including a brand new hardware fault.

I'd be surprised if a hardware fault manifested itself as truncating data in a particular column, rather than just crapping all over the data ...

Some things to try perhaps:

Look in the Event Log for the server for anything "iffy" ...
... and in the SQL Log too

Run a DBCC CHECKDB on the database to see if there is any corruption

I would look for any recently changes Sprocs or Triggers. Of course the data could change by something which was NOT a trigger / Sproc, but it might alert you that something changed that you have either forgotten, or were not aware of

SELECT TOP 100 modify_date, type_desc, name
FROM	sys.objects AS O
ORDER BY modify_date DESC
3

It sounds as if some code is truncating the value, probably because on an expression in the code or an implied length that ends up truncating the data to that length.

I'd look carefully at any db or related code that was modified recently.

4

Those lengths smell like someone has created a varchar variable without declaring the length. Varchar variables should always be created with the maximum length required specifically stated.

5

Thanks for your reply guys. I've checked my sp and dumbed it down in an attempt to isolate the problem. Even in this version it does the same thing - truncates after 30 characters in the MessageBodyPlain field. Here's the sp. (Sorry I don't remember how to format the code for this post). When I print the @SQL query to the screen and run the command I can see the @SQL query is truncating the data.

-- stored procedure ---

@ProfileId numeric,
@NewsletterId numeric,
@MessageId numeric,
@Priority tinyint,
@RecipientListId numeric,
@ExcludeListId numeric,
@FromAddress varchar(50),
@FromName varchar(50),
@DateSent datetime,
@MessageSubject varchar(50),
@MessageBodyPlain varchar(MAX),
@MessageBodyHTML varchar(MAX),
@MessageAttachment varchar(MAX) = NULL

AS
BEGIN

DECLARE @SQL As varchar(MAX)

SET @SQL = 'UPDATE CSKEMAILMESSAGES

SET


MessageBodyPlain = ''' + CAST(@MessageBodyPlain As Varchar) + ''''


If @MessageAttachment IS NOT NULL BEGIN
	SET @SQL = @SQL + ' , MessageAttachment = ''' +  @MessageAttachment  + ''''
END

SET @SQL = @SQL + ' WHERE (MessageId = ' + CAST(@MessageId As Varchar)+ ') AND (ProfileID = '+ CAST(@ProfileId AS VarChar)+ ')'


EXECUTE(@SQL)

PRINT(@SQL)
END

This is what I'm sending to the sp...

USE [mydb]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_sp1]
@ProfileId = 1,
@NewsletterId = 1,
@MessageId = 1,
@Priority = 1,
@RecipientListId = 13,
@ExcludeListId = 1,
@FromAddress = N'xxxxxxxxxxxxxxx',
@FromName = N'xxxxxxxxxxxx',
@DateSent = N'3/8/17',
@MessageSubject = N'test',
@MessageBodyPlain = N'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
@MessageBodyHTML = N'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
@MessageAttachment = NULL

SELECT 'Return Value' = @return_value

GO

This is what the sp returns as the SQL that was submitted. Notice the MessageBodyPlain has been truncated to 30 characters.

(1 row(s) affected)
UPDATE CSKEMAILMESSAGES

SET

MessageBodyPlain = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' WHERE (MessageId = 1) AND (ProfileID = 1)

(1 row(s) affected)

I don't understand why it's truncating to 30 characters. The field is set for varchar(MAX). I've tried changing it to varchar(2000), and other various datatypes, dropped the table, recreated it, dropped the sp and recreated it. All with the same result.

I'm at a loss.

6

There you go. Specifying a varchar without it's length.

1 Like
7

Ah! Thanks!! Didn't even see that. Made the change and problem solved.

What's curious however is how the sp worked for 2 months without issue and without change. Then a couple of days ago it started acting up.

I appreciate your help. Thanks guys.

8

I have seen poor third party code where strings are not given lengths.
Sometimes when the SPs compile it seems to be able to work out the length and at other times it just assumes a length of about 30.
I presume your SP compiled with a reasonable length the first time and then something like a STATS change caused a re-compile which assumed a length of about 30.

Ideally T-SQL should just throw an error if a string does not have a length or at least have something like a lint utility as Kristen has requested.

Anyway, the whole approach in this SP is wrong. For dynamic SQL use sp_executesql. Something like:

DECLARE @SQL varchar(MAX);
SELECT @SQL =
    'UPDATE CSKEMAILMESSAGES' + CHAR(13) + CHAR(10)
    + 'SET MessageBodyPlain = @MessageBodyPlain' + CHAR(13) + CHAR(10)
    + CASE
        WHEN @MessageAttachment IS NOT NULL
        THEN CHAR(9) + ', MessageAttachment = @MessageAttachment' + CHAR(13) + CHAR(10)
        ELSE ''
    END
    + 'WHERE MessageId = @MessageId' + CHAR(13) + CHAR(10)
    + CHAR(9) + 'AND ProfileID = @ProfileId;';

EXEC sp_executesql @SQL
    ,'@MessageBodyPlain varchar(MAX), @MessageAttachment varchar(MAX), @MessageId int, @ProfileId int'
    ,@MessageBodyPlain, @MessageAttachment, @MessageId, @ProfileId;

Personally I would just use static sql. Something like:

UPDATE CSKEMAILMESSAGES
SET MessageBodyPlain = @MessageBodyPlain
    ,MessageAttachment = COALESCE(@MessageAttachment, MessageAttachment)
WHERE MessageId = @MessageId
    AND ProfileID = @ProfileId;
1 Like
9

Thanks for your input.