Huge Database

hiya
I did search on this but didnt turn up much.

I recently upgraded to 3.5 and the database has grown from 550mb to 797mb !!! and am getting warning messages from my host..

I went from v2.6 to 3.5 and also installed a theme http://snuggs.me.uk

My pictures are held in files not in the database

Is there anything I can to to reduce the database size ? I did truncate the Db using my hosts tools.

Is it normal for a database to grow this big ? My site doesnt have thousands of products.

I am a newbie at this so replies need to be simple :-)

Regards
Richard

Are you running the sched task to delete Guest accounts?

snuggtopz wrote:

Hi Richard,

The only way to find out cause is to understand what is taking up this space in your db.

Use this script to find what is taking up the space in your DB. Share the results so we can help you with the same.

Regards

Hi There,

It might be possible that you are storing images in database.
Please check Media Setting.

If you are storing images in database then the database size will be increased.

You can change it to file system from Media setting.

hiya
thanks for the feedback guys, i really appreciate it. that script is very handy indeed thank you.
and yes the images are stored on the host not in the Database, i had that problem once before :-)

here is what I got ..
TableName  indexName  RowCounts  TotalPages  UsedPages  DataPages  TotalSpaceMB  UsedSpaceMB  DataSpaceMB
AclRecord  PK__AclRecor__3214EC077E75F509  0  0  0  0  0  0  0
ActivityLog  PK__Activity__3214EC077F60ED59  8080  121  90  88  0  0  0
ActivityLogType  PK__Activity__3214EC0703317E3D  53  2  2  1  0  0  0
Address  PK__Address__3214EC0707020F21  10455  145  105  103  1  0  0
AddressAttribute  PK__AddressA__3214EC07A5A3BF79  0  0  0  0  0  0  0
AddressAttributeValue  PK__AddressA__3214EC07DE1CE822  0  0  0  0  0  0  0
Affiliate  PK__Affiliat__3214EC070AD2A005  2  2  2  1  0  0  0
BackInStockSubscription  PK__BackInSt__3214EC070EA330E9  0  0  0  0  0  0  0
BlogComment  PK__Tmp_Blog__3214EC07D936CBC0  0  0  0  0  0  0  0
BlogPost  PK__BlogPost__3214EC07164452B1  12  6  6  3  0  0  0
Campaign  PK__Campaign__3214EC071A14E395  6  3  3  1  0  0  0
Category  PK__Category__3214EC071DE57479  471  17  12  10  0  0  0
CategoryTemplate  PK__Category__3214EC0721B6055D  2  2  2  1  0  0  0
CheckoutAttribute  PK__Checkout__3214EC0725869641  3  2  2  1  0  0  0
CheckoutAttributeValue  PK__Checkout__3214EC0729572725  0  0  0  0  0  0  0
Country  PK__Country__3214EC072D27B809  236  4  4  2  0  0  0
CrossSellProduct  PK__CrossSel__3214EC0730F848ED  157  2  2  1  0  0  0
Currency  PK__Currency__3214EC0734C8D9D1  11  2  2  1  0  0  0
Customer  PK__Customer__3214EC0738996AB5  5997  1657  1507  1499  12  11  11
Customer_CustomerRole_Mapping  PK__Customer__ABACF0F7440B1D61  2006  769  649  645  6  5  5
CustomerAddresses  PK__Customer__3C8958223C69FB99  1154  8  8  6  0  0  0
CustomerAttribute  PK__Customer__3214EC07BF692DC5  0  0  0  0  0  0  0
CustomerAttributeValue  PK__Customer__3214EC07676B7735  0  0  0  0  0  0  0
CustomerRole  PK__Customer__3214EC0747DBAE45  6  2  2  1  0  0  0
DeliveryDate  PK__Delivery__3214EC079A9776A5  3  2  2  1  0  0  0
Discount  PK__Discount__3214EC074BAC3F29  3  2  2  1  0  0  0
Discount_AppliedToCategories  PK__Discount__9AC84AD24F7CD00D  0  0  0  0  0  0  0
Discount_AppliedToProducts  PK__Discount__D5903DBF7313696E  0  0  0  0  0  0  0
DiscountRequirement  PK__Discount__3214EC07571DF1D5  6  2  2  1  0  0  0
DiscountUsageHistory  PK__Discount__3214EC075AEE82B9  0  0  0  0  0  0  0
Download  PK__Download__3214EC075EBF139D  6  187  181  1  1  1  0
EmailAccount  PK__EmailAcc__3214EC07628FA481  4  2  2  1  0  0  0
ExternalAuthenticationRecord  PK__External__3214EC0766603565  0  0  0  0  0  0  0
Forums_Forum  PK__Forums_F__3214EC076A30C649  0  2  2  1  0  0  0
Forums_Group  PK__Forums_G__3214EC076E01572D  0  2  2  1  0  0  0
Forums_Post  PK__Forums_P__3214EC0771D1E811  0  4  4  1  0  0  0
Forums_PrivateMessage  PK__Forums_P__3214EC0758D1301D  3  2  2  1  0  0  0
Forums_Subscription  PK__Forums_S__3214EC0775A278F5  4  2  2  1  0  0  0
Forums_Topic  PK__Forums_T__3214EC07797309D9  0  3  3  1  0  0  0
GenericAttribute  PK__GenericA__3214EC077D439ABD  23490  9953  1946  1815  77  15  14
GiftCard  PK__GiftCard__3214EC0701142BA1  0  0  0  0  0  0  0
GiftCardUsageHistory  PK__GiftCard__3214EC0704E4BC85  0  0  0  0  0  0  0
GoogleProduct  PK__GooglePr__3214EC07436BFEE3  0  0  0  0  0  0  0
Language  PK__Language__3214EC0708B54D69  1  2  2  1  0  0  0
LocaleStringResource  PK__LocaleSt__3214EC070C85DE4D  15171  129  122  120  1  0  0
LocalizedProperty  PK__Localize__3214EC0710566F31  0  0  0  0  0  0  0
Log  PK__Log__3214EC0714270015  67398  13610  13496  13276  106  105  103
MailChimpEventQueueRecord  PK__MailChim__3214EC073F9B6DFF  0  2  2  1  0  0  0
Manufacturer  PK__Manufact__3214EC0717F790F9  255  4  4  2  0  0  0
ManufacturerTemplate  PK__Manufact__3214EC071BC821DD  1  2  2  1  0  0  0
MeasureDimension  PK__MeasureD__3214EC071F98B2C1  4  2  2  1  0  0  0
MeasureWeight  PK__MeasureW__3214EC07236943A5  4  2  2  1  0  0  0
MessageTemplate  PK__MessageT__3214EC072739D489  96  8  8  6  0  0  0
News  PK__News__3214EC072EDAF651  12  9  9  1  0  0  0
NewsComment  PK__Tmp_News__3214EC07821BE65D  0  0  0  0  0  0  0
NewsLetterSubscription  PK__NewsLett__3214EC0732AB8735  91  4  4  2  0  0  0
Order  PK__Order__3214EC07367C1819  3363  105  85  83  0  0  0
OrderItem  PK__OrderPro__3214EC073E1D39E1  7440  177  165  163  1  1  1
OrderNote  PK__OrderNot__3214EC073A4CA8FD  26464  1009  901  897  7  7  7
PermissionRecord  PK__Permissi__3214EC0741EDCAC5  153  2  2  1  0  0  0
PermissionRecord_Role_Mapping  PK__Permissi__4804FB2645BE5BA9  70  2  2  1  0  0  0
Picture  PK__Picture__3214EC07498EEC8D  6909  474  424  421  3  3  3
Poll  PK__Poll__3214EC074D5F7D71  0  2  2  1  0  0  0
PollAnswer  PK__PollAnsw__3214EC0751300E55  0  2  2  1  0  0  0
PollVotingRecord  PK__Tmp_Poll__3214EC073AC4AE19  0  0  0  0  0  0  0
Product  PK__Product__3214EC075CA1C101  3369  440  388  376  3  3  2
Product_Category_Mapping  PK__Product___3214EC076442E2C9  1206  6  6  4  0  0  0
Product_Manufacturer_Mapping  PK__Product___3214EC07681373AD  162  2  2  1  0  0  0
Product_Picture_Mapping  PK__Product___3214EC076BE40491  1368  7  7  5  0  0  0
Product_ProductAttribute_Mapping  PK__ProductV__3214EC070A688BB1  420  2  2  1  0  0  0
Product_ProductTag_Mapping  PK__Product___5802448C7EF6D905  103  2  2  1  0  0  0
Product_SpecificationAttribute_Mapping  PK__Product___3214EC077755B73D  0  0  0  0  0  0  0
ProductAttribute  PK__ProductA__3214EC07607251E5  18  2  2  1  0  0  0
ProductAttributeCombination  PK__ProductV__3214EC070E391C95  0  2  2  1  0  0  0
ProductAttributeValue  PK__ProductV__3214EC071209AD79  1602  17  11  9  0  0  0
ProductReview  PK__Tmp_Prod__3214EC0797A1E09D  3  2  2  1  0  0  0
ProductReviewHelpfulness  PK__Tmp_Prod__3214EC07869B6EB5  0  0  0  0  0  0  0
Products_Excell  NULL  1971  203  188  182  1  1  1
ProductTag  PK__ProductT__3214EC077B264821  86  2  2  1  0  0  0
ProductTemplate  PK__ProductT__3214EC0702C769E9  2  2  2  1  0  0  0
ProductWarehouseInventory  PK__ProductW__3214EC07A33A9D78  0  0  0  0  0  0  0
QueuedEmail  PK__QueuedEm__3214EC0715DA3E5D  26454  5002  4830  4494  39  37  35
RecurringPayment  PK__Recurrin__3214EC0719AACF41  0  0  0  0  0  0  0
RecurringPaymentHistory  PK__Recurrin__3214EC071D7B6025  0  0  0  0  0  0  0
RelatedProduct  PK__RelatedP__3214EC07214BF109  1206  6  6  4  0  0  0
ReturnRequest  PK__ReturnRe__3214EC07251C81ED  3  2  2  1  0  0  0
RewardPointsHistory  PK__RewardPo__3214EC0728ED12D1  0  0  0  0  0  0  0
ScheduleTask  PK__Schedule__3214EC072CBDA3B5  24  2  2  1  0  0  0
SearchTerm  PK__SearchTe__3214EC0773E7F563  30  2  2  1  0  0  0
Setting  PK__Setting__3214EC07308E3499  450  25  16  14  0  0  0
Shipment  PK__Shipment__3214EC07345EC57D  2994  9  9  7  0  0  0
ShipmentItem  PK__Shipment__3214EC07382F5661  2254  17  16  14  0  0  0
ShippingByWeight  PK__Shipping__3214EC07473C8FC7  93  4  4  2  0  0  0
ShippingMethod  PK__Shipping__3214EC073BFFE745  21  2  2  1  0  0  0
ShippingMethodRestrictions  PK__Shipping__9CE6B8E13FD07829  1414  7  7  5  0  0  0
ShoppingCartItem  PK__Shopping__3214EC0743A1090D  2001  57  40  38  0  0  0
ShoppingCartReminderQueueRecord  PK__Shopping__3214EC07567ED357  0  0  0  0  0  0  0
ShoppingCartReminderWhiteList  PK__Shopping__3214EC075A4F643B  4  2  2  1  0  0  0
SpecificationAttribute  PK__Specific__3214EC07477199F1  0  2  2  1  0  0  0
SpecificationAttributeOption  PK__Specific__3214EC074B422AD5  0  0  0  0  0  0  0
StateProvince  PK__StatePro__3214EC074F12BBB9  155  4  4  2  0  0  0
Store  PK__Store__3214EC07944643D1  2  2  2  1  0  0  0
StoreMapping  PK__StoreMap__3214EC07848EE2A8  0  0  0  0  0  0  0
TaxCategory  PK__TaxCateg__3214EC0752E34C9D  5  2  2  1  0  0  0
TaxRate  PK__TaxRate__3214EC074B0D20AB  0  0  0  0  0  0  0
TierPrice  PK__TierPric__3214EC0756B3DD81  1  2  2  1  0  0  0
Topic  PK__Topic__3214EC075A846E65  39  18  16  5  0  0  0
UrlRecord  PK__UrlRecor__3214EC0773B7646C  1386  33  31  29  0  0  0
Vendor  PK__Vendor__3214EC07CB58C598  0  0  0  0  0  0  0
Warehouse  PK__Warehous__3214EC07CF409F0E  0  0  0  0  0  0  0

I did find this in my host software

Log File Size, KB:   77824

My biggest problem is I don't know how to find it and even if I should delete it..
I have however now deleted guest accounts and also the old shopping carts and exported files ..

Also using the host software MyLittleAdmin for SQL I found a button called Shrink Database !! what the hell I pressed it not knowing what was going to happen ??

All is well with the world :-)  Database size now 302mb and the log is 1024kb !! if only I could find a shrink button that good for my wifes bottom then Life would be great heheh..

All i need now is to try and speed up the page loading times ..

thank you for your help guys .. you all rock .

Richard

Hi Richard

No you don't want to delete it !!! all you need to do is Shrink it in your SSMS, right click your database, select Task > Shrink > Files, then select Log from the File type dropdown, and make sure "Release unused space" is selected and then select OK, and that is it, and that will take right down for you.

Ah sorry I i did not read the last bit :(, just leave it now and just check it once in a while to make sure it has not grown any more.

Kind Regards
Ron Palmer

hehe thanks Ron and everyone else too, that shrink button works wonders. I was a bit worried it might mess things up but it did the job even for a muppet like me  :-)

So my friends until the next time i mess up nopcom
adiós y gracias por todos los peces...

Hi there!

This is indexes problem. We had an 800MB database and now it has only 150MB.

Take a look: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Run the B opntion (Rebuild or reorganize all indexes with fragmentation and update modified statistics on all user databases) and be happy!

You can use SQL shrink later, of course.

Ivan.