SQL Database Size & Issues |
Post Reply |
Author | |
Randy
Guest Group |
Post Options
Thanks(0)
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? |
|
kspare
Senior Member Joined: 26 January 2005 Location: Canada Status: Offline Points: 334 |
Post Options
Thanks(0)
|
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.
|
|
Randy
Guest Group |
Post Options
Thanks(0)
|
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
|
|
Desperado
Senior Member Joined: 27 January 2005 Location: United States Status: Offline Points: 1143 |
Post Options
Thanks(0)
|
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
|
|
Randy
Guest Group |
Post Options
Thanks(0)
|
Dan, Thanks for the information. Is your database runing about this big? 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. |
|
Desperado
Senior Member Joined: 27 January 2005 Location: United States Status: Offline Points: 1143 |
Post Options
Thanks(0)
|
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. |
|
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.320 seconds.