I am writing to a strange issue that we came across while upgrading a nopCommerce 3.80 website to nopCommerce 4.10. I am also writing the solution that we did to resolve this issue.
Problem:
While upgrading, I was checking database size regularly and found that database size increased from 1569 MB to 2927 MB when I executed upgrade script to upgrade database from nopCommerce 4.00 to nopCommerce 4.10.
This was very surprising for me and we started looking for problems. We did same procedure more one time and result was again same.
Database size was increasing to almost double while upgrading from nopCommerce 4.00 to nopCommerce 4.10. Which forced me to look in to upgrade script and then I found following code at line number 3814
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PictureBinary]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
BEGIN
EXEC('CREATE TABLE [dbo].[PictureBinary]
(
[Id] int IDENTITY(1,1) NOT NULL,
[PictureId] int NOT NULL,
[BinaryData] [varbinary](max) NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
--copy existing data
INSERT INTO [dbo].[PictureBinary](PictureId, BinaryData)
SELECT [Id], [PictureBinary] FROM [dbo].[Picture]
ALTER TABLE dbo.Picture DROP COLUMN [PictureBinary]')
END
GO
I checked in my new database. I had 24847 records in my Picture table. Then I checked size of Picture and PictureBinary table using following code:
EXEC sp_spaceused [Picture];
EXEC sp_spaceused [PictureBinary];
It gave me following result:
name rows reserved data index_size unused
Picture 24847 1251152 KB 1250832 KB 88 KB 232 KB
PictureBinary 24847 1247760 KB 1247256 KB 72 KB 432 KB
Solution:
Then I executed following script:
ALTER INDEX ALL ON [Picture] REBUILD;
EXEC sp_spaceused [Picture];
It gave me following result:
name rows reserved data index_size unused
Picture 24847 3600 KB 3496 KB 16 KB 88 KB
It was shocking for me. Picture table was reduced to 3 MB from 1250 MB. This is because dropping "PictureBinary" column in Picture table does never clears data. We need to re-index table.
Hope NopCommerce team can now add "ALTER INDEX ALL ON [Picture] REBUILD;" at Line number 3836 in their nopCommerce 4.10 upgrade script.
