How to reduce the size of the database file to under 4GB

Good Day,

Can anyone share some tips on purging data to reduce the size of the DB file to under 4GB so the offline function can work again?

Due to the limitation of SQL Express one the offline database grows larger than 4GB this failsafe feature is no longer available.

Thanks in advance guys...

Don

Reply to
Don
Loading thread data ...

You will have to delete some old transactions or journal entries.

Backup the current database and keep it in case you need to go back to look-up some old transactions.

Once you delete old transactions reindex and shrink few times that seems to get the size down even more

here is a procedure i use It will delete everything before a specific batch number Hope this helps Backup databse

x === last batch number to keep

run sql Query on Administrator

DECLARE @BatchNumberKeep int

SELECT @BatchNumberKeep = X

DELETE Shipping FROM [Transaction] WHERE BatchNumber < @BatchNumberKeep AND Shipping.TransactionNumber [Transaction].TransactionNumber

DELETE TransactionEntry FROM [Transaction] WHERE BatchNumber < @BatchNumberKeep AND TransactionEntry.TransactionNumber = [Transaction].TransactionNumber

DELETE TaxEntry FROM [Transaction] WHERE BatchNumber < @BatchNumberKeep AND TaxEntry.TransactionNumber [Transaction].TransactionNumber

DELETE SignatureCapture FROM TenderEntry WHERE BatchNumber < @BatchNumberKeep AND SignatureCapture.TenderEntryID = TenderEntry.ID

DELETE [Transaction] WHERE BatchNumber < @BatchNumberKeep

DELETE Journal WHERE BatchNumber < @BatchNumberKeep

DELETE TaxTotals WHERE BatchNumber < @BatchNumberKeep

DELETE TenderEntry WHERE BatchNumber < @BatchNumberKeep

DELETE TenderTotals WHERE BatchNumber < @BatchNumberKeep

DELETE Batch WHERE BatchNumber < @BatchNumberKeep

dbcc shrinkdatabase (Your Databse name) reindex

"D> Good Day,

Reply to
Robert

Hi Don

Is this just a general question, or is your offline database actually greater than 4GB?

You can check this by connecting to the offline database then go to database

- properties in Store Ops Administrator.

To view the table sizes, to determine which tables are contributing to your db size, run this script.

SELECT [TableName] = so.name, [RowCount] = MAX(si.rows) FROM sysobjects so, sysindexes si WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name) GROUP BY so.name ORDER BY 2 DESC

-- typically the journal is the one table hogging the most space. You can then target non-transactional tables, such as the journal, then shrink and reindex.

H> Good Day,

Reply to
convoluted

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.