• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 504
  • Last Modified:

Back up last three years Data problem (SQL Server 2005)


I'm working on a very large data base which is decreasing due to the large data contained on it Since 2006.
We have decided to keep our customer's data for the only the last 2 years.
I'm trying to run a script that delete all the purshaces's information closed before 2011.
The delete of data is extremely slow and i have more than 60Go of data to delete (there is any pdf or jpeg file on it)
To make it faster i tried to disable :
    1) all the constraint FK
    2) all the indexes
    3) make the database on Simple Mode (disabling statistic,...)
My problem is that the script still too slow ....
Is there anybody who have a best solution to resolve this situation ?

Beside, i was wondering if it it's nos easier to copy my last 2 years data rather than deleting 5 years of data?

Thanks for your precious help
1 Solution
You can try a SELECT INTO statement to copy just the newer data from one table into a new table.   I'm not convinced that doing this will help you, though; duplicating all those rows may just expose your performance problems to a greater extent.

I am curious about why the delete is so slow.  Perhaps the database itself is at the ends of its performance because your disks are too slow, or you don't have enough RAM in your server.  It may be that if you fixed the database performance issue, you wouldn't have to delete old data.

I'm also concerned about your statement, "We have decided to keep our customer's data for the only the last 2 years."   When you say "our customers' data", do you mean your business's data about its customers, or are you hosting data for one or more customers, and you are trying to delete some of that data?  
If it's the first -- are you sure that your business processes will not require you to access that data at some later time (tax audit, contract dispute, etc.)?
If it's the second -- have you cleared with your customers that it is OK to delete their data?  Might they have a need for that "old" data down the road?  What contractual agreements do you have about preserving their data?

A couple of questions:
1) How long since you've backed up your data?
2) Can you put your transaction log on faster disk, even SSD, at least while you are working on the delete?
Jim P.Commented:
This code will delete day-by-day up to thirty days ago from a table. I was writing to a history table so that added transaction time as well.

set nocount on

use [DBName]
-- This section makes a mirror history table that 
-- holds it. Take it out if you don't care ablout it.
if OBJECT_ID('Session_History') is null
	create table dbo.Session_History(
		sessionID			uniqueidentifier	,
		empID				uniqueidentifier	,
		logonTS				datetime			,
		logoffTS			datetime			,
		ip_address			varchar(15)			,
		navigator_agent		char(10)			,
		sessionCptrNm		varchar(32)			,
		lastActivityTS		datetime			,
		facilityID			uniqueidentifier	)

declare	@Stoptime		as	datetime			,
		@ZeroHr			as	datetime			,
		@Midnite		as	datetime			,
		@ThirtyAgo		as	datetime			,
		@Counter		as	integer				,
		@RunStart		as	datetime			,
		@RunStop		as	datetime			,
		@StartCnt		as	integer

-- The @Stoptime is where you want to stop the procedure if it runs too long.
-- The @ThirtyAgo is we only wanted the most recent 30 days in the Seession table.
-- The @RunStart is just to figure out how long it took if we ran it manualy.
select	@Stoptime	=	'2012-08-24 23:00:00.000'		,
		@ThirtyAgo	=	DATEADD(day,-30, GETDATE())		,
		@RunStart	=	GETDATE()						,
		@Counter	=	1

select	@StartCnt	=	COUNT(1)
from	dbo.[Session]

-- The lastActivityTS we knew it had been cleaned up already so it
-- didn't waste days/time.
select	@ZeroHr		=	MIN(lastActivityTS)
from	dbo.[Session]

select	@ZeroHr		=	cast(convert(varchar,@ZeroHr,101) + ' 00:00:00.000' as datetime)	,
		@Midnite	=	cast(convert(varchar,@ZeroHr,101) + ' 23:59:59.000' as datetime)	

while	 (@Stoptime > GETDATE()) 
	while	(@Midnite < @ThirtyAgo) 
--		Delete the lines below below if you don't want the history at all.
		insert into Session_History
		select	*
		from	dbo.[Session] 
		where	lastActivityTS between @ZeroHr and @Midnite
		delete	from	dbo.[Session]
		where	lastActivityTS between @ZeroHr and @Midnite

		print	@Counter + ':  ' + cast(@@rowcount as varchar(25))
-- Add a day to each
	select	@ZeroHr		=	DATEADD(day,1,@ZeroHr)		,
			@Midnite	=	DATEADD(day,1,@Midnite)		,
			@Counter	=	@Counter + 1

select	@RunStop	=	GETDATE()

print	datediff(SECOND ,@RunStart ,  @RunStop)
print	@RunStart
print	@RunStop
print	@StartCnt
select	COUNT (1) as FinalCnt
from	[Session] 


Open in new window

I went from about 50M rows to about 125K in about two hours. Then we ran it on a regular basis. It took about 15 minutes, if that long on a monthly basis. You should be able to modify it to skip the history table.
"1) How long since you've backed up your data?"
I hope the answer is last night or last transaction log backup ran 20 minutes ago.

Why is delete so slow then you must share some details on how you delete.
Some ideas
- setting recovery model from full to simple
- depending on how you approach your data dropping indexes can even slow the delete down
- don't cursor through your data but delete in blocks, blocks between begin and commit transaction
- size of delete's in one block must be tuned in perspective of the available transactionlog
SammoudAuthor Commented:
I want to thank you for your replies,

And answer to some of your questions:
@Jogos: this is the first back up that would be done,
i have done all the recommandation you written in your post.

A great thanks for the script you just send

I want to let you know that im working on a database witch contain more than 500 Tables.
My delete starts at a table Fat, this table is related to more than 20 other tables, that's why i'm disabling the Foreign Key Constraint before the delete.
The Sql Server SGBD doesn't allow the 'with Cascade' Delete...
Don't get the "this is the first backup" in combination with 'since 2006' and all your actions sofar.

When that table has 20 FK's to other tables then you don't need to disable the FK's. If it is referenced by 20 FK's ..... then it is a curious way to start deleting in a relational database.  

If delete is slow then it is good to know what your bottleneck is.... and how it is influenced by your actions.   If it is memory deleting an index used to determine which records will be deleted only will help you in more problems.
If other processes are still using your database (scheduled jobs, reports, your monitoring)  locks can slow it down.
Data and transactionlog on same disk .....
Memory or tempdb of your instance also used for other databases that still are in use on the moment you are deleting.

If you don't share more info on what your are doing and what the bottleneck is then it is hard to help you.  Are you deleting 1000 records in  one action  or 1000 000? What is slow?  

Since your topic is tagged as SQL2005 and you speek of a history since 2006 you may have hardware of that era.  

Since sql2008 there are much improvements that can help in tackling your first problem that your performance is degrading. Better optimiser, better information to monitor ( like DMV's), filtered indexes to not waste space on a large index for a specific approach.  Historical data is maybe also a good reason to implement partitioning.

Decreasing your database know will help you for a while but that environment won't live forever. So maybe it's the time.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now