Solved

Best way to do mass delete of records MSsql server 2005 coldfusion

Posted on 2014-10-01
11
209 Views
Last Modified: 2014-10-01
I want to migrate a database but reduce it first it is 10.5GB  I have a lot of old statistics  records that will reduce this right down. The database is online with customers, CF9. I ran a query from a template but it seems to bring down cold fusion. I have millions of rows to delete. What is the best way?  Thanks. I am not a system engineer - more of a novice in this area.
0
Comment
Question by:Ian White
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 40355671
If you want to empty a table completely, you can enter  
> truncate table BigTableName

That will remove all records.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40355677
Truncate is correct, assuming there's not an identity column that would cause problems if it was re-seeded back to the start (usually 1).

so I'll throw in some other stuff:
*  Do you have this data backed up somewhere, in case you need to un-delete it?
*  Do you have a scheduled outage where you can delete without having to worry about users being in the app mid-deletion?
*  Anything else needs to happen with this delete, such as delete related tables?  If yes, a TRANSACTION is an excellent idea.
0
 

Author Comment

by:Ian White
ID: 40355683
No dont want to empty completely


<CFMODULE TEMPLATE="getdatasource.cfm">
<CFMODULE TEMPLATE='getnewdate.cfm' Days=360>

    <cfquery name="Delete_Old_VisitorStatistics"
				datasource="housecare2" 
				dbtype="ODBC" >
  DELETE FROM VisitorStatistics
    		
    	WHERE
  		VisitorStatistics.VisitDate  < #NewDate# 
	
  </cfquery>

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 65

Expert Comment

by:Jim Horn
ID: 40355689
>No dont want to empty completely
Then DELETE is your only option, as only DELETE supports a WHERE clause where you can delete a subset of rows.
0
 

Author Comment

by:Ian White
ID: 40355691
NO related tables
Yes database backed up nightly
non critical table - rarely accessed by users online
It is written to and records visitors to the site looking at members records
0
 

Author Comment

by:Ian White
ID: 40355698
no schedule outage - but could put message in application.cfm that database is being maintained
0
 

Author Comment

by:Ian White
ID: 40355704
I could bit the bullet and empty the statistics table - it will rebuild

I have another large table notifications that is more critical that will need the where

How long would the truncate take?

I do this in the sql studio? what would be the code to truncate?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40355706
Before attempting the delete, you can add an index to the column in the where clause to try and speed it up

VisitDate  

Alternatively, you could come up with a strategy to move date that you want to keep into a warehouse or archive tables (perhaps in a different database with simple backup strategy).   Once desired records are copied off (using insert from select) then you can truncate the table.   I do this on a weekly basis to keep high er level stats where the details are no longer important
0
 

Author Comment

by:Ian White
ID: 40355708
After the records are gone - does the db auto be a smaller size for the backup? - or does something have to be done - the backup is 10.5 gb - took big to restore zip then ftp. Currently take hours to restore from the nightly backup by the current host
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40355737
in SQL Server Management Studio, right click the database and choose to Shrink File and you can shrink the database file down.
0
 

Author Closing Comment

by:Ian White
ID: 40355969
Thanks that was easy in studio , now just need to check with hosting engineers how to shrink without impacting users - does not appear to be much free space
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question