7/8/2025 7:32:41 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general administration topics for SalesLogix (including LAN & remote topics). View the code of conduct for posting guidelines.
|
|
|
|
Reduce size of db for demo
Posted: 02 Jan 07 9:40 AM
|
fiogf49gjkf0d I have a 10g db I want to shrink to about 2g to put on a latptop for demo. I need to have 10-20 accounts intact with all contacts, opportunities etc. still associated properly. What is the best way to go about this? I tried the purge data wizard in the client, but it is soooooo slooooow. thanks |
|
|
|
Re: Reduce size of db for demo
Posted: 02 Jan 07 10:08 AM
|
fiogf49gjkf0d When I need to do stuff like this I use SQL.
Here is an outline of what I do...
-I identify the accounts I want to keep.
-Use SQL to delete the unwanted accounts.
-Delete the orphaned contact, history, etc. records with SQL statements.
-I work my way through the schema collecting the SQL statements in a script as I go so I can reuse them.
Here is a sample script.
print 'delete account'
delete from account where modifydate<'1-jan-2006'
go
print getdate()
print 'delete accountsummary'
delete from accountsummary
where not exists (select * from account where accountid = accountsummary.accountid)
go
print getdate()
print 'delete gmm_account'
delete from gmm_account
where not exists (select * from account where accountid = gmm_account.accountid)
go
print getdate()
print 'delete gmm_transactions'
delete from gmm_transactions
where not exists (select * from account where accountid = gmm_transactions.accountid)
go
print getdate()
print 'delete contact'
delete from contact
where not exists (select * from account where accountid = contact.accountid)
go
print getdate()
print 'delete gmm_contact'
delete from gmm_contact
where not exists (select * from account where contactid = gmm_contact.contactid)
go
print getdate()
print 'delete account addresses'
delete from address
where entityid like 'a%'
and entityid not in ('ADMIN')
and not exists (select * from account where accountid = address.entityid)
go
print getdate()
print 'delete contact addresses'
delete from address
where entityid like 'c%'
and not exists (select * from contact where contactid = address.entityid)
go
print getdate()
print 'detete account history'
delete from history
where (Accountid<>'' and accountid is not null and not exists (select * from account where accountid = history.accountid))
go
print getdate()
print 'detete contact history'
delete from history
where (Contactid<>'' and contactid is not null and not exists (select * from contact where contactid = history.contactid))
go
print getdate()
print 'delete account association'
delete from association
where (toid like 'a%' and not exists (select * from account where accountid = association.toid))
or (fromid like 'a%' and not exists (select * from account where accountid = association.fromid))
go
print getdate()
print 'delete contact association'
delete from association
where (toid like 'c%' and not exists (select * from contact where contactid = association.toid))
or (fromid like 'c%' and not exists (select * from contact where contactid = association.fromid))
go
print getdate()
print 'delete account adhocgroup'
delete from adhocgroup
where entityid like 'a%'
and not exists (select * from account where accountid = adhocgroup.entityid)
go
print getdate()
print 'delete contact adhocgroup'
delete from adhocgroup
where entityid like 'c%'
and not exists (select * from contact where contactid = adhocgroup.entityid)
go
print getdate()
go
|
|
|
|
Re: Reduce size of db for demo
Posted: 03 Jan 07 11:17 AM
|
fiogf49gjkf0d I do something similar, but different. Here's my procedure:
0) Create a new text database and copy contents from production database
using SQL backup/restore and MakeSafeDatabase.SQL procedure
1) Determine Accounts to keep
Look at Accounts with recent History or recent Activity entries or other criteria.
Select Distinct Top 100 AccountID from History where ModfiyDate > '9/1/2006'
Select Distinct Top 100 AccountID from Activity where ModfiyDate > '9/1/2006'
Export to a text file.
2) Create a new table of AccountIDs to be retained
Create a table named A_Accts2Keep
containing a single column named AccountID.
Import into this table from the text file saved in above step.
3) Create a stored procedure in test database
Copy the following:
CREATE Procedure sp_Delete_Rows_for_Test (@TblName varchar(100))
as
begin
Declare @Q varchar(1000)
set @Q = 'Delete from sysdba.' + @Tblname + ' where AccountID not in (Select AccountID from A_Accts2Keep)'
Execute (@Q)
end
GO
4) Execute stored procedure
Copy the following into Query Analyzer and execute
sp_Delete_Rows_for_Test 'Account'
GO
sp_Delete_Rows_for_Test 'AccountSummary'
GO
sp_Delete_Rows_for_Test 'Activity'
GO
sp_Delete_Rows_for_Test 'C_AccountExt'
GO
sp_Delete_Rows_for_Test 'C_Acct_Items'
GO
sp_Delete_Rows_for_Test 'C_Acct_CheckList'
GO
sp_Delete_Rows_for_Test 'C_Acct_ProcHist'
GO
sp_Delete_Rows_for_Test 'C_Acct_TgtActvLog'
GO
sp_Delete_Rows_for_Test 'C_Time_Statistics'
GO
sp_Delete_Rows_for_Test 'Contact'
GO
sp_Delete_Rows_for_Test 'History'
GO
5) Handle special cases,
Copy the following into Query Analyzer and execute
DELETE FROM sysdba.ADDRESS WHERE ADDRESSID IN
(SELECT AD.ADDRESSID FROM sysdba.ADDRESS AD
LEFT OUTER JOIN sysdba.ACCOUNT A ON AD.ENTITYID = A.ACCOUNTID
WHERE AD.ENTITYID like 'A%' and AD.ENTITYID <> 'ADMIN' and A.ACCOUNTID IS NULL)
GO
DELETE FROM sysdba.ADDRESS WHERE ADDRESSID IN
(SELECT AD.ADDRESSID FROM sysdba.ADDRESS AD
LEFT OUTER JOIN sysdba.CONTACT C ON AD.ENTITYID = C.CONTACTID
WHERE AD.ENTITYID like 'C%' and C.CONTACTID IS NULL)
GO
DELETE FROM sysdba.ADHOCGROUP WHERE ENTITYID IN
(SELECT AD.ENTITYID FROM sysdba.ADHOCGROUP AD
LEFT OUTER JOIN sysdba.ACCOUNT A ON AD.ENTITYID = A.ACCOUNTID
WHERE AD.ENTITYID like 'A%' and A.ACCOUNTID IS NULL)
GO
DELETE FROM sysdba.ADHOCGROUP WHERE ENTITYID IN
(SELECT AD.ENTITYID FROM sysdba.ADHOCGROUP AD
LEFT OUTER JOIN sysdba.CONTACT C ON AD.ENTITYID = C.CONTACTID
WHERE AD.ENTITYID like 'C%' and C.CONTACTID IS NULL)
GO
DELETE FROM sysdba.USER_ACTIVITY WHERE ACTIVITYID IN
(SELECT UA.ACTIVITYID FROM sysdba.USER_ACTIVITY UA
LEFT OUTER JOIN sysdba.ACTIVITY A ON UA.ACTIVITYID = A.ACTIVITYID
WHERE A.ACTIVITYID IS NULL)
GO
TRUNCATE TABLE sysdba.INDEXUPDATES
GO
DELETE FROM sysdba.PLUGIN
WHERE (RELEASED <> 'T')
GO
DELETE FROM sysdba.HISTORY
WHERE (STARTDATE < '9/1/2006')
GO
6) Shrink the database
Create and execute a Maintenance Plan running the Optimization step only
and selecting minimum free space.
Shrink the database by either
Copying the following into Query Analyzer and execute
dbcc shrinkdatabase (TestSlx, 0)
or
running Shrink option from Enterprice Manager and selecting
option to move used pages to front of file.
|
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|