I am not using the Shopping Cart neither the forums. Moreover i have got only 100 registered users.
However one thing that is funny is that my Users table contains 80000 rows. Strange. However when i login as admin and view the users, it is about 100.
You said "If you don't need to store any information at all about users and you're not using the eCommerce module or the Forum, you could eliminate the Profile system altogether". Any idea what changes will i have to do and which files i will have to replace.
However one thing that is funny is that my Users table contains 80000 rows. Strange. However when i login as admin and view the users, it is about 100.
Houston, you have a problem. [:O]
The reason this is happening is that the app uses anonymous identification. You'll notice this in the web.config:
This is required for your app to take advantage of the anonymous profiling features. Of course, this means the app creates a "user" for everyone who comes to your site. What happens is that, after a while, your DB contains thousands and thousands of junk
records. Ouch.
Briefly, this is how it works: whenever someone comes to the site, the system creates an anonymous user and sets a cookie on that user's computer. Whenever the user returns to your site, it gets the cookie and uses it to "hook up" that anonymous user record
to that user.
If the user actually registers on the site, the Profile_MigrateAnonymous method in Global.asax will take whatever info is stored in the anonymous profile and switch it to their authenticated profile. It will then delete that anonymous profile. All is good.
But, what happens when a casual user stops by, but never comes back? Or, if they come back, but only after that cookie on their computer has expired? You've now got a permanent junk record clogging up your DB. You can see how this could really add up after
a while.
If you look into the actual user table data, you will see a column called IsAnonymous. Notice that most of them are set to "true". Now, look at the column marked "LastActivityDate". For any records with LastActivityDate older than 30 days, that means that
the visitor never registered on your site, AND their cookie has already expired. That's an orphan record that you don't need or want hanging around.
If you use anonymous Profiles, you MUST have a way for finding and deleting all of these records. Otherwise, your DB will just keep inflating indefinitely. Of course, they unfortunately don't mention this in the book, nor does ASP.NET system have a convenient
way of doing this. In my opinion, this should have been built into the framework from the get-go. Sadly, it was not.
You need a procedure to find all Anonymous users older than 30 days and delete them, along with their anonymous Profile, Personalization, and Roles information. There is a good article that contains a DB script that takes care of this:
The problem is that when i delete records from the db, the size increases more. My hosting provider says that i am using simple recovery mode. So my db does not use log file, hence i cannot shrink log files.
Tell me something, what is the worst that could happen if i truncate my profiles table?
The problem is that when i delete records from the db, the size increases more. My hosting provider says that i am using simple recovery mode. So my db does not use log file, hence i cannot shrink log files.
We're not talking about log files here. These are actual records, which is different. If you delete your anonymous users, your database will be way, way smaller.
ronnieedin
Tell me something, what is the worst that could happen if i truncate my profiles table?
No one can say without seeing your app. However, I have to be honest here -- I don't think blindly truncating tables is a very smart approach. First, I would follow the suggestion I already made about cleaning up the anonymous records. Second, if you don't
need the anonymous identification features (which it sounds like you don't) I would turn anonymous identification off in your web.config. Third, I would get rid of the Profile fields that you're not using.
I think basically it would, yes. But I would take a look at the link I posted earlier. This guy has a really rock-solid query that will take care of what you need to do. And just as importantly, he includes parameters that allow you to specify how many records
to delete at one time.
Remember, this can be an intensive operation that could basically slow your site to an unusable crawl for several hours. For that reason alone, I'd want to be able to perform this in small increments... say, 1,000 records at a time. And probably pretty late
at night if possible.
ronnieedin
Member
31 Points
111 Posts
Re: aspnet_Profile table
Feb 21, 2008 02:27 PM|LINK
Hi,
Wow. What a crisp answer. Thanks.
I am not using the Shopping Cart neither the forums. Moreover i have got only 100 registered users.
However one thing that is funny is that my Users table contains 80000 rows. Strange. However when i login as admin and view the users, it is about 100.
You said "If you don't need to store any information at all about users and you're not using the eCommerce module or the Forum, you could eliminate the Profile system altogether". Any idea what changes will i have to do and which files i will have to replace.
thanks.
Lee Dumond
Contributor
6404 Points
1173 Posts
Re: aspnet_Profile table
Feb 21, 2008 04:07 PM|LINK
Houston, you have a problem. [:O]
The reason this is happening is that the app uses anonymous identification. You'll notice this in the web.config:
<anonymousIdentification cookieless="AutoDetect" enabled="true"/>
This is required for your app to take advantage of the anonymous profiling features. Of course, this means the app creates a "user" for everyone who comes to your site. What happens is that, after a while, your DB contains thousands and thousands of junk records. Ouch.
Briefly, this is how it works: whenever someone comes to the site, the system creates an anonymous user and sets a cookie on that user's computer. Whenever the user returns to your site, it gets the cookie and uses it to "hook up" that anonymous user record to that user.
If the user actually registers on the site, the Profile_MigrateAnonymous method in Global.asax will take whatever info is stored in the anonymous profile and switch it to their authenticated profile. It will then delete that anonymous profile. All is good.
But, what happens when a casual user stops by, but never comes back? Or, if they come back, but only after that cookie on their computer has expired? You've now got a permanent junk record clogging up your DB. You can see how this could really add up after a while.
If you look into the actual user table data, you will see a column called IsAnonymous. Notice that most of them are set to "true". Now, look at the column marked "LastActivityDate". For any records with LastActivityDate older than 30 days, that means that the visitor never registered on your site, AND their cookie has already expired. That's an orphan record that you don't need or want hanging around.
If you use anonymous Profiles, you MUST have a way for finding and deleting all of these records. Otherwise, your DB will just keep inflating indefinitely. Of course, they unfortunately don't mention this in the book, nor does ASP.NET system have a convenient way of doing this. In my opinion, this should have been built into the framework from the get-go. Sadly, it was not.
You need a procedure to find all Anonymous users older than 30 days and delete them, along with their anonymous Profile, Personalization, and Roles information. There is a good article that contains a DB script that takes care of this:
http://msmvps.com/blogs/omar/archive/2007/03/25/cleanup-inactive-anonymous-users-from-asp-net-membership-tables.aspx
If you do this, your DB should shrink enough that you won't have to mess with your application further.
Hope this helps.
Follow Me on Twitter
ronnieedin
Member
31 Points
111 Posts
Re: aspnet_Profile table
Feb 21, 2008 04:20 PM|LINK
Hi,
The problem is that when i delete records from the db, the size increases more. My hosting provider says that i am using simple recovery mode. So my db does not use log file, hence i cannot shrink log files.
Tell me something, what is the worst that could happen if i truncate my profiles table?
thanks.
Lee Dumond
Contributor
6404 Points
1173 Posts
Re: aspnet_Profile table
Feb 21, 2008 05:16 PM|LINK
We're not talking about log files here. These are actual records, which is different. If you delete your anonymous users, your database will be way, way smaller.
No one can say without seeing your app. However, I have to be honest here -- I don't think blindly truncating tables is a very smart approach. First, I would follow the suggestion I already made about cleaning up the anonymous records. Second, if you don't need the anonymous identification features (which it sounds like you don't) I would turn anonymous identification off in your web.config. Third, I would get rid of the Profile fields that you're not using.
Follow Me on Twitter
ronnieedin
Member
31 Points
111 Posts
Re: aspnet_Profile table
Feb 22, 2008 01:47 AM|LINK
Thank you buddy for being so helpful and patient throughout. I will try out your suggestions.
[ EDIT]
I came out with these queries.
select count (*) from aspnet_profile
INNER
JOINaspnet_Users
ON aspnet_Profile.UserId = aspnet_Users.UserIdWHERE
(aspnet_Users.IsAnonymous = 1)-- Returns 70300 rows
DELETE
FROM aspnet_ProfileFROM
aspnet_Profile INNER JOINaspnet_Users
ON aspnet_Profile.UserId = aspnet_Users.UserIdWHERE
(aspnet_Users.IsAnonymous = 1)and then
delete
from aspnet_Userswhere
IsAnonymous = 1Do you think this will do?
thanks.
ronnieedin
Member
31 Points
111 Posts
Re: aspnet_Profile table
Feb 22, 2008 02:09 AM|LINK
Hi,
I came out with these queries.
select count (*) from aspnet_profile
INNER
JOINaspnet_Users
ON aspnet_Profile.UserId = aspnet_Users.UserIdWHERE
(aspnet_Users.IsAnonymous = 1)-- Returns 70300 rows
DELETE
FROM aspnet_ProfileFROM
aspnet_Profile INNER JOINaspnet_Users
ON aspnet_Profile.UserId = aspnet_Users.UserIdWHERE
(aspnet_Users.IsAnonymous = 1)and then
delete
from aspnet_Userswhere
IsAnonymous = 1Do you think this will do?
thanks.
Lee Dumond
Contributor
6404 Points
1173 Posts
Re: aspnet_Profile table
Feb 22, 2008 03:31 AM|LINK
I think basically it would, yes. But I would take a look at the link I posted earlier. This guy has a really rock-solid query that will take care of what you need to do. And just as importantly, he includes parameters that allow you to specify how many records to delete at one time.
Remember, this can be an intensive operation that could basically slow your site to an unusable crawl for several hours. For that reason alone, I'd want to be able to perform this in small increments... say, 1,000 records at a time. And probably pretty late at night if possible.
Follow Me on Twitter