Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2002-07-15 : 17:23:41
|
| I've got a database that stores user email. Like most email admins, I'm starting to crack down on runaway user mailboxes. I'd like to limit each user to 1MB of space.The obvious way would be to have the SP that inserts email do the insert, then check mailbox size, then delete old email until the overall size is 1MB or less.The email is stored in a text column. I can get the size of a user's mailbox with something like "select sum(datalength(content)) from email where u_to=[user_id]". However, that's painfully slow, and I worry about the load on the databse (a *lot* of email goes through this system). Is there a better way of doing this? I guess I could add an int column and set it to the datalength during inserts, but that seems inelegant. Any other ideas?Thanks-b |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-07-15 : 18:43:07
|
| I'd probably go with the int column thing. It's not very elegant, but I think that's a good solution given that there is a "LOT" of email that goes through the system.You could add an field to the User table that keeps track of his current "mailbox size" and use a trigger on the "mail" table to populate that field.Michael<Yoda>Use the Search page you must. Find the answer you will. |
 |
|
|
|
|
|
Faster way than select sum(datalength(column))?