Field overflow and Log grow

Hi,
I am using SQL Server 2000.
One user database contains a table as following:
CREATE TABLE Events..Audit_Sub (
[RecID] [bigint] NOT NULL ,
[Name] [varchar] (100) NULL ,
[Value] [varchar] (1024) NULL
) ON [PRIMARY]
Sometimes the application writes to the table a record that overflows the
size of the Value field (actually because of an error in the new code, the
appilcation attempts to write about 5Kb to the Valaue field).
The fact is:
- No error is detected on SQL Server, data is written to tha table, it is
visible by select, it is just truncated to the field size (1Kb).
- In the meanwhile it appears that DB log begins growing: the things are not
directly dependent, just somewhat later the log begins growing, but no error
is found in SQL errorlog.
- Later on transaction log cannot be backup up, data are no more written to
DB, but then it is too late to understand the reason why.
The question is:
- In which way may the two things (overflow and log grow) be related?
- What really happens on SQL server when data overflow occurs? How does it
handle?
Thanks in advance,
MRMarco
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - In which way may the two things (overflow and log grow) be related?
It does not matter whether or not overflow occured. the log file grows up
and it is not truncated unless you have SIMPLE recovery mode the database
set
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
create table t (c1 tinyint, c2 varchar (5))
--owerflow on c1 column
insert into t values(4545745454545,'a')
--Server: Msg 8115, Level 16, State 2, Line 1
--Arithmetic overflow error converting expression to data type tinyint.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
--now insert much more characters than you defined for c2 columnn
insert into t values(1,'asgtbvtybvtg')
--Server: Msg 8152, Level 16, State 9, Line 1
--String or binary data would be truncated.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
"Marco Roda" <mrtest@.amdosoft.com> wrote in message
news:e8t656$le6$1@.ss408.t-com.hr...
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written
> to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||Hi
At a guess you have the ANSI_WARNINGS setting off as "When OFF, data is
truncated to the size of the column and the statement succeeds. " e.g
SET ANSI_WARNINGS ON
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.
----
Transaction Rolled Back Error Status: 8152
(1 row(s) affected)
col1
--
(0 row(s) affected)
*/
SET ANSI_WARNINGS OFF
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
(1 row(s) affected)
Transaction Comitted
col1
--
A
(1 row(s) affected)
*/
although with your log file growing it may be that you have detected an
error and not rolled back the transaction, use DBCC OPENTRAN to view open
transactions.
John
"Marco Roda" wrote:
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are not
> directly dependent, just somewhat later the log begins growing, but no error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uozopRApGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Marco
> > - In the meanwhile it appears that DB log begins growing: the things are
> > not
> > directly dependent, just somewhat later the log begins growing, but no
> > error
> > is found in SQL errorlog.
> > - In which way may the two things (overflow and log grow) be related?
>
> It does not matter whether or not overflow occured. the log file grows up
> and it is not truncated unless you have SIMPLE recovery mode the database
> set
>
> > - What really happens on SQL server when data overflow occurs? How does
it
> > handle?
>
> create table t (c1 tinyint, c2 varchar (5))
> --owerflow on c1 column
> insert into t values(4545745454545,'a')
> --Server: Msg 8115, Level 16, State 2, Line 1
> --Arithmetic overflow error converting expression to data type tinyint.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
> --now insert much more characters than you defined for c2 columnn
> insert into t values(1,'asgtbvtybvtg')
> --Server: Msg 8152, Level 16, State 9, Line 1
> --String or binary data would be truncated.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
>
The fact is: when the application attempts writing more data, data is REALLY
WRITTEN (even if truncated), and NO ERROR is thrown.
- Why did not get error?
- May the overflow be a reason why the log is growing?