Sql – How many users can a 200mb ASP.NET membership database hold

asp.net-membershipsql server

I just purchased an unlimited shared hosting windows account on godaddy with 2 databases only to realise that they have a 200mb limit on SQL Server databases. My website uses the automatically generated ASP.NET 2.0 membership database. The nature of my website (free classifieds) requires the storage of a significant number of users.

Since I'm using the standard ASP.NET membership database that (hopefully) many of you have used before… I was wondering just how many users a 200 mb database could hold?

Best Answer

Depends on your other information in the ASP.NET membership table (applications, roles etc.) - but I did a quick experiment to get a rough idea:

Users      Disk space used on disk (MB)
------      ----------------------------
 5'000                5.5 MB
10'000                8.9 MB
25'000               18.1 MB
50'000               33.8 MB

If you do a rough estimate, this means, with 200 MB limit you should be able to support 250'000 users or more - enough for you?

Marc

Related Solutions

Sql-server – How to remove duplicate rows

Assuming no nulls, you GROUP BY the unique columns, and SELECT the MIN (or MAX) RowId as the row to keep. Then, just delete everything that didn't have a row id:

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

In case you have a GUID instead of an integer, you can replace

MIN(RowId)

with

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))

Sql-server – How to foreign key constraints be temporarily disabled using T-SQL

If you want to disable all constraints in the database just run this code:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To switch them back on, run: (the print is optional of course and it is just listing the tables)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).

If you are deleting all the data you may find this solution to be helpful.

Also sometimes it is handy to disable all triggers as well, you can see the complete solution here.

Best Answer

Related Solutions

Sql-server – How to remove duplicate rows

Sql-server – How to foreign key constraints be temporarily disabled using T-SQL

Related Topic