Asp.net Membership tables will grow automatically when you use shopping cart sites. After some period the database size will full and you will get the error as
"Could not allocate space for object 'dbo.aspnet_Profile'.'PK__aspnet_Profile__2057CCD0' in database '' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."
So to delete use the below query in stored procedures or by using triggers to perform delete operation
Second method by using triggers to delete
=================================
"Could not allocate space for object 'dbo.aspnet_Profile'.'PK__aspnet_Profile__2057CCD0' in database '' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."
So to delete use the below query in stored procedures or by using triggers to perform delete operation
DECLARE
@Days int
SET
@Days =
21
DECLARE
@NoOfUsersToDelete int
SET
@NoOfUsersToDelete =
1000
IF
EXISTS
(SELECT
*
FROM
sys.objects
WHERE
object_id
=
OBJECT_ID(N'[dbo].[PagesToDelete]')
AND
type
in
(N'U'))
DROP
TABLE
[dbo].[PagesToDelete]
IF
EXISTS
(SELECT
*
FROM
sys.objects
WHERE
object_id
=
OBJECT_ID(N'[dbo].[aspnetUsersToDelete]')
AND
type
in
(N'U'))
DROP
TABLE
[dbo].[AspnetUsersToDelete]
create
table
PagesToDelete
(PageID
int
NOT
NULL
PRIMARY
KEY)
create
table
AspnetUsersToDelete
(UserID
uniqueidentifier
NOT
NULL
PRIMARY
KEY)
insert
into
AspnetUsersToDelete
select
top(@NoOfUsersToDelete)
UserID from
aspnet_Users where
(isAnonymous
=
1)
and
(LastActivityDate
<
(getDate()-@Days))
order
by
UserID
print
'Users
to delete: '
+
convert(varchar(255),@@ROWCOUNT)
GO
insert
into
PagesToDelete
select
ID from
Page where
UserID in
(
select
UserID from
AspnetUsersToDelete
)
print
'Pages
to delete: '
+
convert(varchar(255),@@ROWCOUNT)
GO
delete
from
WidgetInstance where
PageID IN
(
SELECT
PageID FROM
PagesToDelete )
print
'Widget
Instances deleted: '
+
convert(varchar(255),
@@ROWCOUNT)
GO
delete
from
Page where
ID IN
(
SELECT
PageID FROM
PagesToDelete )
GO
delete
from
UserSetting WHERE
UserID IN
(
SELECT
UserID FROm
AspnetUsersToDelete )
GO
delete
from
aspnet_Profile WHERE
UserID IN
(
SELECT
UserID FROm
AspnetUsersToDelete )
GO
delete
from
aspnet_UsersInRoles WHERE
UserID IN
(
SELECT
UserID FROm
AspnetUsersToDelete )
GO
delete
from
aspnet_PersonalizationPerUser WHERE
UserID IN
(
SELECT
UserID FROm
AspnetUsersToDelete )
GO
delete
from
aspnet_users where
userID IN
(
SELECT
UserID FROm
AspnetUsersToDelete )
PRINT
'Users
deleted: '
+
convert(varchar(255),
@@ROWCOUNT)
GO
drop
table
PagesToDelete
drop
table
AspnetUsersToDelete
GO
=================================Second method by using triggers to delete
=================================
CREATE
TRIGGER
DELETE_Aspprofileanduserstable
on
aspnet_Profile
after
update
as
begin
Delete
from
aspnet_profile
where
userid in
(
select
userid from
aspnet_users
where
isanonymous =
1
and
datediff(dd,
lastactivitydate,
getdate())>
14)
Delete
from
aspnet_users
where
userid in
(
select
userid from
aspnet_users
where
isanonymous =
1
and
datediff(dd,
lastactivitydate,
getdate())>
14)
end
go
0 comments:
Post a Comment