January 10, 2013 at 2:31 pm
Hi Experts,
can some one give me a query which deletes the data from a table(Condition is delete June-December data) of a year,
Here the data is very large, so i am looking for a query which deletes the data in bacthes without logging every delete.
Thanks in advance
your help is much appreciated
January 10, 2013 at 2:48 pm
Well, it'll probably look something like this:
DELETE FROM dbo.MyTable
WHERE MyDateColumn >= '2012-06-01' AND MyDateColumn < '2013-01-01';
SQL Server will log every row row deleted. Has to in order to maintain ACITity of the database. (Google "database ACID" if you aren't familiar with that concept.)
If the database is in Simple recovery, you can break the delete down into smaller batches, and checkpoints will dump those from the log once they're complete. (It's a bit more complex than that, but it's accurate enough.)
If the database is in Full or Bulk Logged recovery, you can still break the delete down into smaller batches, but you'll need to backup the log file to get the transactions to clear from it. That might not save you any time, overall, but it might have other advantages. Like, do you have more room for log backups than you do for log files, on your drives?
The easiest way to break it up is to add a TOP clause to the delete statement. That'll only work if you're using SQL 2008 or later. You posted in the SQL 2008 forum, but I hate to assume on that point. If so, it'll look like:
DELETE TOP (1000) FROM dbo.MyTable
WHERE MyDateColumn >= '2012-06-01' AND MyDateColumn < '2013-01-01';
You might need to fiddle with the number of rows to delete per batch. 1000 is just a number I picked, it's not necessarily the best number if your situation. Only way to know would be to access your server and try out some batch sizes and see what works best.
I can't get more specific without more data from you, like the table name, the database recovery model, business rules regarding data loss policies, and disk space available for backups vs log files, at the very minimum. Even better would be the number of rows you expect to delete, and the structure of the table (create scripts, not just typing column names in the forum).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply