MS SQL server - db transaction error |
Post Reply 
|
| Author | |
peet 
Newbie 
Joined: 01 August 2007 Location: United States Status: Offline Points: 21 |
 Post Options
 Thanks(0)
 Quote Reply
 Topic: MS SQL server - db transaction errorPosted: 10 April 2011 at 2:52am |
|
I'm running the 4.2.4.844 version, and recently(~1 week ago) it started having hickups. Some mail get through and some are stuck in quarantine temp folder.
In the activity log I see:
04/09/11 23:37:29:164 -- (2708) Error occurred during TAddToQuarantineThread when accessing the quarantine database: [Microsoft][ODBC SQL Server Driver]String data, right truncation ( 1 83 84 85 86 86a 86b 86c 87 88 89 90 91 92 93 94 94b)
What does this error mean?
Database file is 1.3Gigs but I know it has been up at 6Gigs long time back so I know the database isn't hitting a max limit.
So what gives?
Thanks,
-Peet
|
|
 |
|
|
LogSat
Admin Group 
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
Posted: 10 April 2011 at 10:53am |
|
peet,
Could you please open the table tblQuarantine in the database, go to the last row, and let us know what the value for the field "MsgID" is? In addition, can you please zip and email us SpamFilter's activity logfile for today, along with all the contents of the quarantine temp folder? If the zip is over 8MB in size, I'll email you via a PM the credential to upload the file on our ftp site.
|
|
|
|
|
peet
Newbie
Joined: 01 August 2007 Location: United States Status: Offline Points: 21 |
Post Options
Thanks(0)
Quote Reply
Posted: 13 April 2011 at 4:36am |
|
LogSat - I didn't have logging on, so I've just turned it on. I'll take a few minutes of it and zip it up and upload it shortly. Looks like my quarantine has over 7000 files... zipped
In the tblQuarantine when sorted by QuarID desc the highest QuarID is 25713459. For MsgID it has 24461141 MsgDate being 4/13/2011 1:24:27 So it seems toa dd.. yet quarantine folder has a lot, and log shows errors. I'll upload the quarantine and log as peet.zip
|
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
Posted: 13 April 2011 at 10:18pm |
|
peet,
We received your files, but are still unable to replicate the issue. Could you please email us at support @ logsat dot com your SpamFilter.ini file so we can look at your settings (specifically your database connection string)? If you are using a .udl file to specify your database connection string, please include that in the zip as well.
Edited by LogSat - 13 April 2011 at 10:18pm |
|
|
|
|
peet
Newbie
Joined: 01 August 2007 Location: United States Status: Offline Points: 21 |
Post Options
Thanks(0)
Quote Reply
Posted: 14 April 2011 at 6:31pm |
|
Logsat - I've sent an e-mail with the two files attached.
My logs now being on are almost half a gig per day. I don't suppose you'd want that uploaded to your FTP. Quarantine has now over 7000 files in it. MS SQL db is about 1.3gigs, but it has been up at 6gigs before, so I don't think that could have an issue. -Peet
|
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
Posted: 15 April 2011 at 12:26am |
|
peet,
I just sent you an email as follows: ========= While we still can't reproduce the issue, from the errors and the symptoms this may be an issue caused by the ODBC driver for SQL Server (your connection string appears to use the "Microsoft OLE DB Provider for ODBC Drivers"). Instead of creating an ODBC DSN entry in the ODBC Data Sources, can you please change your database connection string to use the "Microsoft OLE DB Provider for SQL Server" instead (see screenshot)? This will bypass the ODBC DSNs, and will allow you to specify directly the SQL connection settings. Alternatively, you could use a UDL file to specify the connection. However, the UDL file you attached us had been modified to replace the original OLEDB Provider for SQL to an ODBC provider, thus possibly causing the same issues. I'm including the default sample SpamFilterMSSQL.udl file in a zip in case you wish to revert to that. Please do not modify the "Provider" in it, but just customize the "Connection" details on it. Please let me know if this works, |
|
|
|
|
peet
Newbie
Joined: 01 August 2007 Location: United States Status: Offline Points: 21 |
Post Options
Thanks(0)
Quote Reply
Posted: 19 April 2011 at 5:33pm |
|
I've replaced the DB connection string with the UDL file for connection, and it did reduce the daily log file sizes from 300+megs to 15megs or so.
But the error is still happening. I sent you an e-mail with more details. The database went from 1.3GB to almost 4GB now. Quarantine folder has over 400 files again. Any ideas? Thanks, -Peet
|
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
Posted: 21 April 2011 at 11:37pm |
|
For Google... While monitoring peet's SQL Server we saw it responding very, very slow to queries performed against the SpamFilter database. The deletion of expired emails, even when performed with manual queries, resulted in timeouts and failure to delete records. The tblQuarantine table contained around 30,000 records, while the tblMsgs contained almost 9 million of then, thus indicating a huge amount of orphaned records. Restarting SQL Server solved the performance issue, which was probably caused to begin with by:
(1) having imposed a limit on the size of the transaction logfile for SpamFilter's database without clearing it routinely, which caused the transaction log to fill up. (2) having disabled SpamFilter's database cleanup routine, and implementing one via scheduled stored procedures on SQL Server, but imposing a max limit of number of rows that were deleted by the custom cleanup routine.
Edited by LogSat - 21 April 2011 at 11:37pm |
|
|
|
Topic Options|
Post Reply
|
|
Tweet
|
| Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |
This page was generated in 0.227 seconds.