Remove anonymous users from ASP.Net Membership tables

If you use the ASP.Net membership tools, have <anonymousIdentification enabled="true" /> specified in your Web.config, and get lots of anonymous visitors, it’s only a matter of time before your database grows. What’s filling it up is the countless user records for your anonymous users.

If you don’t need to track user and profile information for an anonymous user once they leave the site, you can delete the unneeded data by running a SQL script. The following script will delete from your membership tables all anonymous users whose last activity was more than 7 days ago.

delete from aspnet_profile
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) > 7
)

delete from aspnet_usersinroles
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) > 7
)

delete from aspnet_membership
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) > 7
)

delete from aspnet_personalizationperuser
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) > 7
)

delete from aspnet_users
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) > 7
)

Doing this may be very important to those using a shared hosting plan that has limited SQL disk space, as those anonymous users can quickly eat up disk space. Since each row in aspnet_users takes up just over 1kb, having 1,000 anonymous users will eat up 1MB — something that adds up quick if you only have 250MB of SQL disk storage.

Popularity: 12% [?]

Related posts:

1. Deleting all tables and constraints in a database
2. Scripting the deletion of objects in a SQL database (second version)
3. On disk space and defragmentation
4. CSS Adapters for Membership Controls (working versions)
5. Forcing users to choose a browser other than Internet Explorer doesn’t help them