Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - MS SQL server - db transaction error
  FAQ FAQ  Forum Search   Register Register  Login Login

MS SQL server - db transaction error

 Post Reply Post Reply
Author
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4068
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Topic: MS SQL server - db transaction error
    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
Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
peet View Drop Down
Newbie
Newbie
Avatar

Joined: 01 August 2007
Location: United States
Status: Offline
Points: 21
Post Options Post Options   Thanks (0) Thanks(0)   Quote peet Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4068
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post 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,
Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
peet View Drop Down
Newbie
Newbie
Avatar

Joined: 01 August 2007
Location: United States
Status: Offline
Points: 21
Post Options Post Options   Thanks (0) Thanks(0)   Quote peet Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4068
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post 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
Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
peet View Drop Down
Newbie
Newbie
Avatar

Joined: 01 August 2007
Location: United States
Status: Offline
Points: 21
Post Options Post Options   Thanks (0) Thanks(0)   Quote peet Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4068
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post 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.
Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
peet View Drop Down
Newbie
Newbie
Avatar

Joined: 01 August 2007
Location: United States
Status: Offline
Points: 21
Post Options Post Options   Thanks (0) Thanks(0)   Quote peet Quote  Post ReplyReply Direct Link To This Post Posted: 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
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.047 seconds.