How to reduce database size?

Hi,

I couldn't find information related do this topic, so I apollogize if that's already been asked.

I don't really know why but my database grows a lot and it does it quite quick. I reached the 100mb of the sql server 2012 db my host provider gives for free.

I do perform shrinks and it helps up to a point but currently my database is about 140mb (with only 250 products). I tryed to delete some log information, genericAttribute table data... and that helped to reduce the database size.

But my question is, does anybody have the same problem? Is there a way I could get rid of more "garbage"? How do you deal with the growing size? I may need a sort of database configuration...I'm a bit lost.

Besides, Is there any host provider that allows sql server databases up to 100mb for free or a cheap price?

Thanks in advance

UPDATE:

After deleting the log directly from the db and the genericattributte tables data I got about 100mb of decrease. OMG!!!
I wasn't aware of that. Do you perform this task periodically or/and automatically?

I hope you didn't delete all records in GenericAtrribute.  (Just for non-existent customers)

There is no automatic log clear.  It's best to periodically look at logs to see if there are any problems (although it would be nice to get rid of the .php related messages :)
DELETE [Log] WHERE ShortMessage like '%.php%'  

I'm not SQL expert, but from what I've read, it's not a good idea to keep shrinking your db, especially if it just grows back to that size again.

How big are your pictures?  I like having pics in th eDB (others don't), but maybe keeping them in files is better in your case if the file space they give you is separate from DB space.

Here's query to see space used by DB

exec sp_spaceused

and this script will show space used per table

create table ##temp(nam varchar(50), rows int, res varchar(15),data varchar(15),ind_sze varchar(15),unsed varchar(15))
insert into ##temp exec sp_msforeachtable 'exec sp_spaceused [?]'
select nam Table_Name,rows Total_Rows,res Total_Table_Size,data Data_size,ind_sze Index_Size,unsed Unused_Space from ##temp order by CONVERT(INT, replace(res,' KB','')) desc
drop table ##temp


Two low cost hosts that I've used with virtually no DB size limit (10GB I think) would be Arvixe, or SmarterAsp.NET

Hi New York,

Thanks for your reply.

Yes I was careful about that. I found another topic in which Andrei suggested to do the following:

DELETE FROM [GenericAttribute]
WHERE [EntityId] NOT IN (SELECT c.[Id] FROM [Customer] c WITH (NOLOCK)) and
[KeyGroup]=N'Customer'
GO


I don't save images into bd as to avoid growing database size, but it seems I should have been more careful about "logs" and other temp data, that made my db to grow too much.

Best,

First of all you need to know what took place. This may be data or log. Depending on that you will choose what to do. to reduce the data or log.

This will delete orphan pictures that don't have associated products mapped

delete Picture 
from Product_Picture_Mapping AS PPM RIGHT OUTER JOIN
  Picture AS P ON PPM.PictureId = P.Id
WHERE PPM.Id IS NULL;

This query will show which pictures are the largest:

SELECT PPM.ProductId, P.ID as picture_id, DATALENGTH(PictureBinary)/1024 as pic_size, P.MimeType
FROM Product_Picture_Mapping AS PPM with(nolock) INNER JOIN
  Picture AS P with(nolock) ON PPM.PictureId = P.Id
order by pic_size desc;

You can then use tinypng.com to shrink .png or .jpg files without loss of quality.

Interestingly, despite the age of this post, this is now relevant to me.

My hosting provider has just recently suspended my account due to the DB size. Upon querying, the DB had reached 500mb+. Upon looking in to this, 450mb or so was the log file. Specifically with the content:

The controller for path '/addproducttocart/catalog/20/' was not found or does not implement IController.


A public action method 'AddProductToCart_Details' was not found on controller 'Nop.Web.Controllers.ShoppingCartController'.

This cannot be a normal routine that the DB exponentially grows like this. The site is one month old and only has 75 odd products on it all with only one image. The DB is completely down to the log file

after we change the image storage from db to file system so we need to delete images from database?
I am running a site migrated regularly from nop2.3 not on nop 3.8, my db size has swelled up to 4GB I have only 150 active products.
I changed the image storage to file system, still there is no change in the db size. please advise.

Kakoli