Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - SQL Database Size & Issues
  FAQ FAQ  Forum Search   Register Register  Login Login

SQL Database Size & Issues

 Post Reply Post Reply
Author
Randy View Drop Down
Guest Group
Guest Group
Post Options Post Options   Thanks (0) Thanks(0)   Quote Randy Quote  Post ReplyReply Direct Link To This Post Topic: SQL Database Size & Issues
    Posted: 14 December 2004 at 1:11am

Are current SQL database runs on a Dual 3 Ghz, 2GB RAM Server (Server is standalone just for the Logsat DB)..  We are processing about 300,000 blocked messages a day.  The database is about 2.5 million records, and 10 GB in size.  I am starting to have deadlocking issues, and I am considering writing code to move the data out to several database and manage through changes in the web site.  Do you have any suggestions or advice?  Anyone have larger databases? Maybe I need a few indexes to correct problems?

Back to Top
kspare View Drop Down
Senior Member
Senior Member


Joined: 26 January 2005
Location: Canada
Status: Offline
Points: 334
Post Options Post Options   Thanks (0) Thanks(0)   Quote kspare Quote  Post ReplyReply Direct Link To This Post Posted: 14 December 2004 at 8:12am

I had the same problem, on a smaller scale, so I now run a transaction log backup 2 times a day and compact the database 4 times a day and that made a HUGE difference.

I also run an sql script that will automatically delete 90% of the garbage if your interested.

 

Back to Top
Randy View Drop Down
Guest Group
Guest Group
Post Options Post Options   Thanks (0) Thanks(0)   Quote Randy Quote  Post ReplyReply Direct Link To This Post Posted: 14 December 2004 at 11:58am
Kevin, thanks for the info.  Could you email the script your using?  I would be interested in taking a look at it.  rsieren@iowasolutions.com
Back to Top
Desperado View Drop Down
Senior Member
Senior Member
Avatar

Joined: 27 January 2005
Location: United States
Status: Offline
Points: 1143
Post Options Post Options   Thanks (0) Thanks(0)   Quote Desperado Quote  Post ReplyReply Direct Link To This Post Posted: 14 December 2004 at 4:54pm
Randy,
 
I am fairly sure you know enough to have done this BUT ... you want to make sure the "Recovery Model" is set to Simple where as the default is "Full"  This helps the efficiency also.  Another issue is to make sure that the DB doesn't have to constantly grow and shrink by setting the size options.  Another issue is the "worker threads".  MS sets this to 255 by default but all the "experts" claim that for the type of application the SpamFilter is, that is WAY too high.   Mine is set to 150.  One other issue is to have the RAM usage stable.  Figure out how much you actually require and set the memory option to use that an not dynamically change.   All the "Dynamic" settings are fine for a lightly loaded server but not in a case like yours.
 
Last issue ... Think about the "Enterprise" edition of MSSQL.  It is a much more robust beast but you really pay for it.
 
Probably no help but there you have it.
 
Dan Seligmann
 
Back to Top
Randy View Drop Down
Guest Group
Guest Group
Post Options Post Options   Thanks (0) Thanks(0)   Quote Randy Quote  Post ReplyReply Direct Link To This Post Posted: 14 December 2004 at 5:03pm

Dan,

Thanks for the information.  Is your database runing about this big?
We process about 350,000 emails a day, and keep 15 days.

I am concerned of not knowing are high I can go before running into issues.  I am planning to begin to split differnet domains to use different SQL Database, but I have one domain that process 300K by itself.  So it doesn't help me much to split them up.

Back to Top
Desperado View Drop Down
Senior Member
Senior Member
Avatar

Joined: 27 January 2005
Location: United States
Status: Offline
Points: 1143
Post Options Post Options   Thanks (0) Thanks(0)   Quote Desperado Quote  Post ReplyReply Direct Link To This Post Posted: 14 December 2004 at 5:14pm

Randy,

No ... I am running around 120K / day TOTAL and save for 8 days.  DB Size is only around 4.2 GIG with around 500K records.

Dan S.

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.320 seconds.