Thread: Database Growing Very Fast

I only have a 500 meg database allocation. Our old store i held 30,000 orders and customers under 200 megs.

With ASPDNSF 9.2 i'm alway fighting to keep the database under 500megs.

Dbo.Customer 1,282 records at 216,120KB Data
Dbo.Orders 931 records at 209,320KB Data

Between both of them ONLY it's over 400megs

What the hell. Any help would be great.

Please Help

It's probably dbo.Profile that's growing at an enormous rate. Check for other threads here on how to keep it in check.

I second that - it's probably dbo.profile that's the cause.

Also, in some non-US stores, the ASPDNSF maintenance procedure didn't delete old rows in dbo.profile correctly because of date locale differences (e.g. dd/mm not mm/dd).

Not sure if this has been corrected yet in 9.x

Adam

Found the thread on how to correct this:

http://forums.aspdotnetstorefront.co...ase-table-size

Originally Posted by webopius

I second that - it's probably dbo.profile that's the cause.

Also, in some non-US stores, the ASPDNSF maintenance procedure didn't delete old rows in dbo.profile correctly because of date locale differences (e.g. dd/mm not mm/dd).

Not sure if this has been corrected yet in 9.x

Adam

See the screen shot below. Not even 1000 orders and 216megs ? Hot do i reduce it on both customers and orders? Store relatively new.

Is there a scrip to break down disk usage in a table buy coulomb?

What columns are causing Dbo.Customers and Dbo.Orders to be so big?

Running out of space again.

250K per record

Without knowing what plugins/gateways/shipping settings it is hard to say exactly which columns are using the most data. But given what I have seen before there is a high chance that the RTShipResponse and RTShipRequest have a decent amount of data. These columns hold shipping debug information. If you are not having issues with shipping you can set both columns to null.

As for finding how large each column is in table you could try doing a sum(datalength(columname)) for each column in the table.

Thanks so much for the information.

RTShipResponse was by far the largest from my column at 207060906

RT shipping is currently working fine, how do i Null it with SQL management studio? And delete all current information in that column?

Thanks A bunch

Would the below be the command??

Update customers set RTShipResponse = NULL
Update customers set RTShipRequest = NULL

Yes the mentioned sql statements should work just fine. Just keep in mind once the columns are made null it is harder to know for sure how RT Shipping got rate it gave.

Thanks for the response, I need SQL for dummies.

To confirm: when I set to:
Update customers set RTShipResponse = NULL

Does that mean all data in that column past, present and future will be gone?

How do i put it back to normal ?

Future data would begin to grow again. Past data would be removed and you could not get it back again unless you took some precaution by backing it up first. You could either back up the whole database or export just those tables, or you could export just the table primary key and those columns.

Thanks,

It worked great. I cut my database size by 80% because of those 2 columns.

I would be great to get then on the monthly maintainance.

You can always edit the monthly maintenance stored procedure [aspdnsf_MonthlyMaintenance].

Originally Posted by mmcgeachy

As for finding how large each column is in table you could try doing a sum(datalength(columname)) for each column in the table.

If you have proper permissions the following script will give you a detailed breakdown of table space utilization. This script also displays index size since sometimes rebuilding/reorganizing an index can free up space. (However, more often rebuilding an index will actually increase its size in the file with the benefit of increased performance.)

Code:

CREATE TABLE #tablesize
    (
     [name] NVARCHAR(120)
    ,[data] VARCHAR(18)
    ,[reserved] VARCHAR(18)
    ,[index_size] VARCHAR(18)
    ,[unused] VARCHAR(18)
    ,[rows] CHAR(11)
    )

INSERT  INTO #tablesize
        EXEC sp_msforeachtable @command1 = 'exec sp_spaceused ''?'''
SELECT
	 [name] AS [table-name]
	,CAST(RTRIM(REPLACE([data],'KB','')) AS INT) AS [data (KB)]
	,CAST(RTRIM(REPLACE([reserved],'KB','')) AS INT) AS [reserved (KB)]
	,CAST(RTRIM(REPLACE([index_size],'KB','')) AS INT) AS [index-size (KB)]
	,CAST(RTRIM(REPLACE([unused],'KB','')) AS INT) AS [unused (KB)]
	,CAST(RTRIM(REPLACE([rows],'KB','')) AS INT) AS [rows]
FROM
    #tablesize
ORDER BY
	 CAST(RTRIM(REPLACE([data],'KB','')) AS INT) DESC
	,[name]

DROP TABLE #tablesize

*

thanks SWillis. your sql query is much easier than mine to see table data. appreciate you posting that.

just wanted to chime in.
i'm a US served website running MS 9.2

the profile db was 13GB and had rows dating back 5 years.

i ran monthly maintenance to clear it in all listed ways (30 days, all, 90 days, etc.). and while the program appears to be running (clock in the admin interface, reset to the login screen), it did not appear to reducing the size of the db. if it did, it was very small (enough that my site running would quickly over-ride)

my thought is that the monthly maintenance is timing out and preventing the profile database table from being properly trimmed.

we ran an sql statement on that table to delete anything over 30 days old, and it appears to have cut our database size by 80%

frustrating! wonder how many server hosting package upgrades this bug has caused to unknowing ASPDNSF users because the db gets so huge it eats all the resource.

Attachment 1597Ok fighting database size again,

After upgrading to Vr 9.3.1 im having the same issues. old remidies are not working.

Biggest problem:
dbo_customers 1843 records Data 181 megs
dbo_orders 1849 records Data 158 megs

With all Nulled out below, What else can i do?

Update customer set RTShipResponse = NULL
Update customer set RTShipRequest = NULL
Update orders set RTShipRequest = NULL
Update orders set RTShipResponse = NULL
Update customer set referrer = NULL
Update orders set referrer = NULL

One row should not be more then 10K Looks empty ? Where is all that data being used?

Ran the above scrip. What does it mean?

Originally Posted by FAC-ADCO

Attachment 1597Ok fighting database size again,

After upgrading to Vr 9.3.1 im having the same issues. old remidies are not working.

Biggest problem:
dbo_customers 1843 records Data 181 megs
dbo_orders 1849 records Data 158 megs

One row should not be more then 10K Looks empty ? Where is all that data being used?

Ran the above scrip. What does it mean?

not sure which table you are talking about when it comes 10k and a row. Or where you are getting the data size from. Given the screen shot I see The customer table with a data size of 1853 KB which is closer to 1.8 mb.

As to what the script means. It is basically similar to the report as what you gave below:


Comparing the two images makes notice that the customer table now have a much higher index size. If you are just trying to make the customer table smaller I would recommend rebuilding/reorganizing indexes just as SWillis talked about.

The below shows after rebiuld and reorg.
Attachment 1600

The below shows after a rebiuld and reorg was preformed.
Attachment 1599


After running Rebuild and reorganize and the total database size grew about 100 Megs.

For kicks i ran a srink database it droped 75 megs. still up 25-30 megs.

500 meg allocation currently at 475 megs.
Any other ideas, i never had any of these problems before. This data base should be 150megs of actual data.

The data size in the database does not justify the size of it.

Any other help or some else to try would be great.

Interesting that going back to the your original report shows different numbers. But that at least gives me something to go with. Can you try this script:

Code:

CREATE TABLE #colsize([name] NVARCHAR(120),[data] INT)

INSERT  INTO #colsize ([name],[data]) SELECT 'CustomerID',sum(datalength([CustomerID])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'CustomerGUID',sum(datalength([CustomerGUID])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'CustomerLevelID',sum(datalength([CustomerLevelID])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'StoreID',sum(datalength([StoreID])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'RegisterDate',sum(datalength([RegisterDate])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'Email',sum(datalength([Email])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'Password',sum(datalength([Password])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'SaltKey',sum(datalength([SaltKey])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'DateOfBirth',sum(datalength([DateOfBirth])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'Gender',sum(datalength([Gender])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'FirstName',sum(datalength([FirstName])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'LastName',sum(datalength([LastName])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'Notes',sum(datalength([Notes])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'SkinID',sum(datalength([SkinID])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'Phone',sum(datalength([Phone])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'FAX',sum(datalength([FAX])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'AffiliateID',sum(datalength([AffiliateID])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'Referrer',sum(datalength([Referrer])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'CouponCode',sum(datalength([CouponCode])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'OkToEmail',sum(datalength([OkToEmail])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'IsAdmin',sum(datalength([IsAdmin])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'BillingEqualsShipping',sum(datalength([BillingEqualsShipping])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'LastIPAddress',sum(datalength(LastIPAddress)) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'OrderNotes',sum(datalength([OrderNotes])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'SubscriptionExpiresOn',sum(datalength([SubscriptionExpiresOn])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'RTShipRequest',sum(datalength([RTShipRequest])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'RTShipResponse',sum(datalength([RTShipResponse])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'OrderOptions',sum(datalength([OrderOptions])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'LocaleSetting',sum(datalength([LocaleSetting])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'MicroPayBalance',sum(datalength([MicroPayBalance])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'RecurringShippingMethodID',sum(datalength([RecurringShippingMethodID])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'RecurringShippingMethod',sum(datalength([RecurringShippingMethod])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'BillingAddressID',sum(datalength([BillingAddressID])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'ShippingAddressID',sum(datalength([ShippingAddressID])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'GiftRegistryGUID',sum(datalength([GiftRegistryGUID])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'GiftRegistryIsAnonymous',sum(datalength(GiftRegistryIsAnonymous)) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'GiftRegistryAllowSearchByOthers',sum(datalength([GiftRegistryAllowSearchByOthers])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'GiftRegistryNickName',sum(datalength([GiftRegistryNickName])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'GiftRegistryHideShippingAddresses',sum(datalength([GiftRegistryHideShippingAddresses])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'CODCompanyCheckAllowed',sum(datalength([CODCompanyCheckAllowed])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'CODNet30Allowed',sum(datalength([CODNet30Allowed])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'ExtensionData',sum(datalength([ExtensionData])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'FinalizationData',sum(datalength([FinalizationData])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'Deleted',sum(datalength([Deleted])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'CreatedOn',sum(datalength([CreatedOn])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'Over13Checked',sum(datalength([Over13Checked])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'CurrencySetting',sum(datalength([CurrencySetting])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'VATSetting',sum(datalength([VATSetting])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'VATRegistrationID',sum(datalength([VATRegistrationID])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'StoreCCInDB',sum(datalength([StoreCCInDB])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'IsRegistered',sum(datalength([IsRegistered])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'LockedUntil',sum(datalength([LockedUntil])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'AdminCanViewCC',sum(datalength([AdminCanViewCC])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'PwdChanged',sum(datalength([PwdChanged])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'BadLoginCount',sum(datalength([BadLoginCount])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'LastBadLogin',sum(datalength([LastBadLogin])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'Active',sum(datalength([Active])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'PwdChangeRequired',sum(datalength([PwdChangeRequired])) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'RequestedPaymentMethod',sum(datalength(RequestedPaymentMethod)) FROM [dbo].[Customer]
INSERT  INTO #colsize ([name],[data]) SELECT 'BuySafe',sum(datalength(BuySafe)) FROM [dbo].[Customer]

SELECT [name],[data] from #colsize order by [data] desc

DROP TABLE #colsize

Mostly wanting to see what the 1st few results are. That way I have an idea for certain which column is taking up the most space.

Wow, results in this format looks great. It looks like RTShipResponse is the largest @ 3.5megs if i read that correctly.


I run the 2 lines below, The below pic is the result.
Update customer set RTShipResponse = NULL
Update customer set RTShipRequest = NULL




What do you think? It looks like it does not sum up? Thanks for helping me out on this.




More Information on customer. Hope it helps Im currently at our quota of 500meg. I need to send Appliedi a support ticket for an extention

Slight disruption, and thankyou for your continued support. I have the results of your last scrip.

ah yes glad see that this post is back. Didn't think it would back given past history... But yes enough about that.

Can you try rebuilding only the PK_Customer index. You can rebuild PK_Customer by right clicking the index then selecting rebuild or checking by the rebuild index in properties window. After that run the index size script report again to see if PK_Customer index size went down. Reason being is the index size of 234520Kb for only 2363 rows which does seem a bit high to me.

If the size has not significantly decreased by doing a rebuild then the next then I would suggest trying is to setting the fill factor to 100% and then rebuild the index. After that run the index size script report again. I don't think having a fill factor of 90% would be causing your database size issue. However I still noticed it was different from what I have setup on my databases setups so thought it was worth trying if the index rebuild does not fix the size issue for the customer table.

Also I know we haven't really gone in depth into why the orders table is so large yet but it easier to focus on one table at time. The process should still be similar enough.

I agree, for 2000 rows the size is large. Below is the results.

Before Anything done 242,648KB
After rebuilding PK 242,640KB
After rebuilding with %100 fill factor = 241,568KB


I have been doing alot of nulling of the RTShipRequest and RTShipresponse in both DBO.Orders & Customer. I was reading an article saying large deletes can grow the PK. I found no reference on repairing that yet.

Have you heard anything like that?
Thanks

Thing is RTShipRequest and RTShipresponse being made null shouldn't effect the index size of . Deleting rows PK_Customer without a rebuild sure but that has been done. I am starting to run out of ideas. Can you try this script and see if the count is higher than zero

Code:

SELECT count(*)  FROM [dbo].[CIM_AddressPaymentProfileMap] pm
WHERE NOT EXISTS (SELECT * FROM [dbo].[Customer]c WHERE c.[CustomerId] = pm.[CustomerId])

I trying to see if foreign key constraint between the these tables is causing the PK_Customer index to be lager. It is a shot in the dark but thought it was worth a investigating given the circumstances.

It came back a 0

Originally Posted by FAC-ADCO

It came back a 0

Hmm darn thought it was worth a shot. The other thing that comes to mind to try is to try to re-crate primary key which again is a long shot since I would of thought rebuilding the index would of done the same thing.

To do this go into management studio and bring up the customer table in design view. Right click the CustomerID column select remove primary key. You will get a prompt saying that CIM_AddressPaymentProfileMap has a relationship since you can re-crate it later click yes. Right click CustomerID again and select set primary key. Click save. The primary key is now re-crated.

To add the relation ship back to CIM_AddressPaymentProfileMap. Bring up the design view of CIM_AddressPaymentProfileMap. Right click on any column and select relationships. A new window will popup Click add. Select the tables and collums specifation to have a ... button show up and click on. Yet another window shows up select the customer a table as the primary key table. On the left and right select CustomerID from the drop downs and click ok. Then click close. Now click the save dialog again. The relation ship is now re-crated.

If that doesn't work the only other thing that comes to mind is making a copy of the table data to new table. Truncating the original table. Then importing the data from the copied table back original table with identity insert turned on. After the import is done drop the copy table. The only problem I see with this is making copy does make the database grow in size that you might not have available due to size limits.

Also Just to note I don't suggest doing either process during peak hours for obvious reasons.

I did all that and no change. ( I did not truncating , this is starting to get way out of my league)


What i did notice is when i null out RTShipRequest and response the column size for those 2 will shed maybe 30-50megs of data which i currently cannot recover that space any way I try.

I was able to recover all that data before the upgrade to 9.3.1

PK Customer continues to grow currently 260megs. 2264 rows

What else can I try?

Ok, so I opened a ticket with Microsoft. We went checking some basic stuff. At this point the database has grown to 600 megs with only 2300 orders and customer. After 1 hour without getting any ware he remote off. Recommendation was to back-up and restore the database to another server. Strangely enough after that process there was 450 megs available. Shrunk it to 170 megs. No explanation as to why just yet.

Our new issue seems to be ReceiptHtml table in dbo.orders

It's very large for what it needs to serve only 4100 rows and 89 megs

How do i script it to be smaller.

is there a very simple receipt that can be used on orders older then 3 months?

Thanks so much

As to how the change the output of the receipt it depends on what is set for XmlPackage.OrderReceipt. The defualt XmlPackage is notification.receipt.xml.config. Do keep in mind modifying the XmlPackage also effects what the customer sees. For that reason I wouldn't recommend having the out put change after a certain time.

You could potentially store the cached output in a file instead however that is easier to do with source. In this case modifying Orders.cs. Just make sure to save the cache as an extension that can't be accessed through a browser. In other words consider saving the files simliar to XmlPackages by saving the files as .config files.

Even without changing the caching you can still trim down some of the size by decent amount be removing indents from the output and excluding two prefixes. Which is just a matter of modifying the XmlPackage. Which would look something like this code wise:

Code:

<xsl:stylesheet version="1.0"
				xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
				xmlns:aspdnsf="urn:aspdnsf"
				xmlns:receipt="urn:receipt"
				xmlns:msxsl="urn:schemas-microsoft-com:xslt"
				exclude-result-prefixes="receipt aspdnsf msxsl" >
			<xsl:output method="html" omit-xml-declaration="yes" encoding="UTF-8" indent="no"/>